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

[Paul Hartland]

Just think of the sub query as a table, so in essence your saying

Select top 5 a.companyname, a.sales
>From yoursubquery as a
Order by a.sales



On Fri, 3 Aug 2018, 01:20 Bill Benson, <bensonforums at gmail.com> wrote:

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