CODEWITHSUNDEEP
sqlsql-serveraggregate-functions
1plus1equals10
1plus1equals10

Reputation: 21

SQL windows function using MAX not giving expected result

So I'm playing around with SQL window functions following on-line help tutorials using AdventureWorks 2012.

I selected out data from the below query into a new table

SELECT sh.CustomerID AS AccountID,
sd.SalesOrderDetailID AS TransactionID,
sh.OrderDate AS TransactionDate,
sd.LineTotal AS Amount
INTO Transactions
FROM sales.SalesOrderHeader sh INNER JOIN sales.SalesOrderDetail sd on
            sh.SalesOrderID = sd.SalesOrderID

Then I ran the following query:

select *,
MIN(Amount) OVER(Partition by AccountID order by transactionDate) AS     MinOrderForDate
from dbo.transactions
Order by AccountID, transactionID desc

Which gave me my expected result:

AccountID   TransactionID   TransactionDate         Amount      MinOrderForDate
11000       63804           2007-11-04 00:00:00.000 53.990000   4.990000
11000       63803           2007-11-04 00:00:00.000 34.990000   4.990000
11000       63802           2007-11-04 00:00:00.000 4.990000    4.990000
11000       63801           2007-11-04 00:00:00.000 28.990000   4.990000
11000       63800           2007-11-04 00:00:00.000 2384.070000 4.990000
11000       38716           2007-07-22 00:00:00.000 21.980000   21.980000
11000       38715           2007-07-22 00:00:00.000 2319.990000 21.980000
11000       449             2005-07-22 00:00:00.000 3399.990000 3399.990000
11001       115673          2008-06-12 00:00:00.000 34.990000   4.990000
11001       115672          2008-06-12 00:00:00.000 8.990000    4.990000
11001       115671          2008-06-12 00:00:00.000 4.990000    4.990000
11001       115670          2008-06-12 00:00:00.000 539.990000  4.990000
11001       38639           2007-07-20 00:00:00.000 8.990000    4.990000
11001       38638           2007-07-20 00:00:00.000 53.990000   4.990000
11001       38637           2007-07-20 00:00:00.000 9.990000    4.990000
11001       38636           2007-07-20 00:00:00.000 4.990000    4.990000
11001       38635           2007-07-20 00:00:00.000 21.980000   4.990000
11001       38634           2007-07-20 00:00:00.000 2319.990000 4.990000
11001       423             2005-07-18 00:00:00.000 3374.990000 3374.990000

But when I replaced MIN with MAX, I keep on getting the MAX value across the whole data range for the account and I can't see why?

select *,
MAX(Amount) OVER(Partition by AccountID order by transactionDate) AS       MaxOrderForDate
from dbo.transactions
Order by AccountID, transactionID desc


AccountID   TransactionID   TransactionDate         Amount      MaxOrderForDate
11000       63804           2007-11-04 00:00:00.000 53.990000   3399.990000
11000       63803           2007-11-04 00:00:00.000 34.990000   3399.990000
11000       63802           2007-11-04 00:00:00.000 4.990000    3399.990000
11000       63801           2007-11-04 00:00:00.000 28.990000   3399.990000
11000       63800           2007-11-04 00:00:00.000 2384.070000 3399.990000
11000       38716           2007-07-22 00:00:00.000 21.980000   3399.990000
11000       38715           2007-07-22 00:00:00.000 2319.990000 3399.990000
11000       449             2005-07-22 00:00:00.000 3399.990000 3399.990000
11001       115673          2008-06-12 00:00:00.000 34.990000   3374.990000
11001       115672          2008-06-12 00:00:00.000 8.990000    3374.990000
11001       115671          2008-06-12 00:00:00.000 4.990000    3374.990000
11001       115670          2008-06-12 00:00:00.000 539.990000  3374.990000
11001       38639           2007-07-20 00:00:00.000 8.990000    3374.990000
11001       38638           2007-07-20 00:00:00.000 53.990000   3374.990000
11001       38637           2007-07-20 00:00:00.000 9.990000    3374.990000
11001       38636           2007-07-20 00:00:00.000 4.990000    3374.990000
11001       38635           2007-07-20 00:00:00.000 21.980000   3374.990000
11001       38634           2007-07-20 00:00:00.000 2319.990000 3374.990000
11001       423             2005-07-18 00:00:00.000 3374.990000 3374.990000

Am I doing this wrong?

Upvotes: 0

Views: 140

Answers (3)

dnoeth
dnoeth

Reputation: 60462

If you order by a non-unique column there's no guaranteed result set.

In your case the result is exactly what you request, a cumulative MIN/MAX, there's a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW automatically added to your query due to the ORDER BY. Additionally the ORDER BY in the Windowed Aggregate Function differs from the final ORDER BY which adds more confusion.

You didn't specify exactly what you're trying to get. Based on the column alias you might want a "group" MIN/MAX:

MIN(Amount) OVER(Partition by AccountID, transactionDate) AS     MinOrderForDate

Upvotes: 2

sstan
sstan

Reputation: 36483

You are not very explicit about your intent, but it sounds like you want the min/max for each account/transactionDate grouping. If that's the case, you probably didn't mean to put transactionDate in the order by clause of the window function.

I think you meant to do this instead:

MIN(Amount) OVER(Partition by AccountID, transactionDate) AS MinOrderForDate
MAX(Amount) OVER(Partition by AccountID, transactionDate) AS MaxOrderForDate

Upvotes: 1

uh_big_mike_boi
uh_big_mike_boi

Reputation: 3470

You are partitioning by AccountID. It's giving you the MAX for that AccountID. For AccountID 11000 the MAX is 3399, for AccountID 11001 it is 3374.

Upvotes: 0

Related Questions