title: aggregation subquery with top N
body: <p>Let's say I have a table called COFFEE showing Bus Stations and all of the coffee shops within 10 blocks of the bus station:</p>
  
  <pre><code>    BusStationID| CoffeeShopID |  Distance (in city blocks)
      1|2103|2
      1|2222|2
      1|8864|7
      1|9920|5
      1|3544|2
      1|4830|2
      1|4823|6
      1|9561|2
      7|6262|2
      7|8561|10
      7|9510|5
      7|2744|1       
      7|4223|9
      7|5960|3
  </code></pre>
  
  <p>[EDITED: to make clear that the question is how to do this with a query not procedurally]</p>
  
  <p>And I have to write a <em>query</em> (not a proc) to show for each bus-station, the average distance to the five closest coffee shops.</p>
  
  <p>I can get the top 5 closest coffee shops for a particular bus-station:</p>
  
  <pre><code>           select avg(top5.distance) as AvgDistToFiveClosest
             from
             (
             select top 5 distance from COFFEE where busstationid = 1
             order by distance
             ) as top5
  </code></pre>
  
  <p>But how do I connect that as a subquery and make AvgDistToFiveClosest a column returned in my main query:</p>
  
  <pre><code>        select BusStationId,  AvgDistToFiveClosest
          from COFFEE...
           ??????
  </code></pre>
  
  <p>Given the sample data above, the query should return:</p>
  
  <pre><code>     BusStationID | AvgDistToFiveClosest
             1 | 2
             7 | 4
  </code></pre>