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

[Bill Benson]

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