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

Paul Hartland paul.hartland at googlemail.com
Thu Aug 2 20:36:26 CDT 2018


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


More information about the AccessD mailing list