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

Paul Hartland paul.hartland at googlemail.com
Thu Aug 2 19:44:21 CDT 2018


No worries, glad to help every now and again as I dont get much chance to &
this list is worth its weight in gold

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

> 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
> >
> --
> 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