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

Bill Benson bensonforums at gmail.com
Thu Aug 2 19:37:59 CDT 2018


OK, that helps, thanks Paul

On Thu, Aug 2, 2018 at 8:31 PM, Paul Hartland via AccessD <
accessd at databaseadvisors.com> wrote:

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


More information about the AccessD mailing list