[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