CODEWITHSUNDEEP
sqlsql-server-2008
dmarra
dmarra

Reputation: 863

How can I get the top 1 value of an aggregate query?

Consider the following:

SELECT TOP 1 discountAmount,
       SUM(amount)
FROM customerTransactions
WHERE customerTrasactions.id = 123
ORDER BY discountAmount DESC

This query definitely will not work, because I am not grouping by discountAmount, but all I want is the sum total of amounts for the customer in question, plus their greatest discount amount.

Is there a way I can do this without having to resort to a subquery or a cross apply? This is a small part of a much larger query that I am trying to optimize, so being able to get these two datapoints in a single query like this would be optimal.

I am unfortunatly constricted to SQL Server 2008

Upvotes: 0

Views: 200

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

You can use window functions:

SELECT TOP (1) discountAmount,
       SUM(amount) OVER () as sum
FROM customerTransactions
WHERE customerTrasactions.id = 123
ORDER BY discountAmount DESC;

The window function provides the sum over all rows that would be returned. That is, the window function is evaluated before the ORDER BY or TOP (1).

Upvotes: 1

Related Questions