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

Bill Benson bensonforums at gmail.com
Thu Aug 2 16:16:32 CDT 2018


Simple task:  Northwind database (a really old one, data from 1998).

The query I wrote to tally gross sales looked like this:

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
Order by Sales Desc


SQL SERVER will not allow me to select the Top N rows from this.

Select Top 5 Cus.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
Order by Sales Desc
)

Someone else rewrote my entire query as follows, which works - but I want
to know why the former method does not work.

Select TOP 5(Cus.CompanyName) 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
Order by Sales Desc


The reason I wanted it my way was so that I could reuse the original
subquery as a separate runable block of code without the Top N interfering.


More information about the AccessD mailing list