Why is the behaviour of min() different from max()?
Select
max(My_Date) over (order by My_Date) as Max_Datum
, min(My_Date) over (order by My_Date) as Min_Datum
From
(
Select dateadd(m,-1,getdate()) as My_Date
Union all
Select getdate() as My_Date
Union all
Select dateadd(m,1,getdate()) as My_Date
Union all
Select dateadd(m,2,getdate()) as My_Date
) t1
Result:
Max_Datum Min_Datum
2024-08-02 16:20:39.733 2024-08-02 16:20:39.733
2024-09-02 16:20:39.733 2024-08-02 16:20:39.733
2024-10-02 16:20:39.733 2024-08-02 16:20:39.733
2024-11-02 16:20:39.733 2024-08-02 16:20:39.733
The problem is not the functions, it's your OVER
clause. You have added an OVER
clause with the clause ORDER BY My_Date
, which, per the documentation, defaults to the window to RANGE UNBOUNDED PRECEDING AND CURRENT ROW
. As a result the MAX
value is always going to be the current rows value, because that's the order the data is in.
Take, for example, the following simplified data:
ID | SomeNumber |
---|---|
1 | 2 |
2 | 3 |
3 | 1 |
4 | 5 |
5 | 4 |
Now let's assume you have a similar query:
SELECT MIN(SomeNumber) OVER (ORDER BY SomeNumber) AS MinSomeNumber,
MAX(SomeNumber) OVER (ORDER BY SomeNumber) AS MaxSomeNumber
FROM dbo.SomeTable
ORDER BY ID;
This would result in the results:
MinSomeNumber | MaxSomeNumber |
---|---|
1 | 2 |
1 | 3 |
1 | 1 |
1 | 5 |
1 | 4 |
Effectively on each row you are asking the following:
So, you can see, that the MAX
will differ, but the MIN
won't.
What you likely want here it to change the ORDER
, specify the window to be the whole dataset, or not specify a window:
SELECT MIN(SomeNumber) OVER (ORDER BY SomeNumber) AS MinSomeNumber,
MAX(SomeNumber) OVER (ORDER BY SomeNumber DESC) AS MaxSomeNumber,
MIN(SomeNumber) OVER (ORDER BY SomeNumber ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MinSomeNumber,
MAX(SomeNumber) OVER (ORDER BY SomeNumber ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MaxSomeNumber,
MIN(SomeNumber) OVER () AS MinSomeNumber,
MAX(SomeNumber) OVER () AS MaxSomeNumber
FROM dbo.SomeTable;
The last likely makes the most "sense" here, as putting the data for the MIN
/MAX
in order of the value you are getting the MIN
/MAX
for doesn't achieve anything. If you were ordering by a different column, it would make more sense.