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

[Paul Hartland]

Try the below, I think you need to remove the order by from the sub query &
put it outside the brackets & name the sub query, see below
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 mysub.Sales Desc

On Thu, 2 Aug 2018, 22:17 Bill Benson, <bensonforums at gmail.com> wrote:

> 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.
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>