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

Bill Benson bensonforums at gmail.com
Thu Aug 2 20:23:40 CDT 2018


 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


More information about the AccessD mailing list