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

Ryan Wehler wrwehler at gmail.com
Thu Aug 2 21:32:34 CDT 2018


Did you happen to clear the query cache between runs? It would be interesting to see if it still comes up with the same plan. 

> On Aug 2, 2018, at 9:26 PM, Bill Benson <bensonforums at gmail.com> wrote:
> 
> I don't know if SQL Server's estimate of query plan is to be believed, but
> it appears that they would both be handled exactly the same at runtime.
> 
> Query Plan Image
> <https://drive.google.com/open?id=1M8f3h-4hAihryx8PlAL5AiKyIlxvXdhT>
> 
>> On Thu, Aug 2, 2018 at 10:17 PM, Bill Benson <bensonforums at gmail.com> wrote:
>> 
>> 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
>>> 
>> 
>> 
> -- 
> 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