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

Bill Benson bensonforums at gmail.com
Thu Aug 2 21:17:56 CDT 2018


Again, thank you Paul!

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

> Yes there's no reason not to use that approach, I was just showing what
> needed to be done to get your query to work, you would probably find your
> colleagues query being more efficient if you viewed and compared the query
> plans on both of them.
>
> Paul
>
> On 3 August 2018 at 02:23, Bill Benson <bensonforums at gmail.com> wrote:
>
> >  I've decided that my colleague's approach (which does not rely on a
> > subquery) is the most efficient. I can't really think of a reason why the
> > subquery needs to hang around in my SQL.
> >
> > My colleague had written
> >
> > Select TOP 5 CompanyName, Sum(Quantity * UnitPrice) as Sales
> >
> > FROM Customers
> >
> >        JOIN Orders
> >
> >               ON Customers.CustomerID = Orders.CustomerID
> >
> >        JOIN [Order Details]
> >
> >               ON [Order Details].OrderID = Orders.OrderID
> >
> > GROUP BY CompanyName
> >
> > ORDER BY Sales Desc
> >
> >
> > Which, on further reflection, feels more efficient than :
> >
> > Select Top 5 CompanyName, Sales
> >
> > FROM (
> >
> >        SELECT CompanyName, Sum(Quantity*UnitPrice) as Sales
> >
> >        FROM Customers
> >
> >               JOIN Orders
> >
> >                      ON Customers.CustomerID = Orders.CustomerID
> >
> >               JOIN [Order Details]
> >
> >                      ON [Order Details].OrderID = Orders.OrderID
> >
> >
> >
> >        GROUP BY CompanyName
> >
> >        ) As CustomerSales
> >
> > ORDER BY Sales Desc
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
>
>
> --
> Paul Hartland
> paul.hartland at googlemail.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