[AccessD] Why Cannot I select Top N in this query

[Bill Benson]

Thanks Paul. So what I am learning from this is that the Order By has to be
outside the subquery. Thing that surprises me about this is that, until the
ordering is done, I don't know how SQL Server knows what I want the "Top 5"
to be. In the query as you rewrote it, if you process the subquery on its
own, you get gross sales in whatever random order SQL decides to run the
query. Then, if you include

Select Top 5  MySub.CompanyName, Sales
From
( .
  .
  .
) as MySub without the Order By clause, you get the first 5 rows,
irrespective of size of Sales.

I don't understand how putting ORDER BY  Sales Desc underneath all of it,
acts to CHANGE the rows that got included in the Top 5. It's as if SQL
Server has changed the subquery's results, not simply reordered them.


 Select Top 5 MySub.CompanyName, Sales
From

(
Select Cus.CompanyName, Sum(Det.Quantity * det.UnitPrice) as Sales

from Customers cus
join Orders Ord on Cus.CustomerID = ord.CustomerID
join dbo.[Order Details] Det on Det.OrderID = ord.OrderID

group by  Cus.CompanyName
) As MySub
Order by Sales Desc