Max() and Min() have different behaviours?

Question

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

Answer 1

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:

1. The lowest number between 1 and 1, and the highest number between 1 and 1
2. The lowest number between 1 and 2, and the highest number between 1 and 2
3. The lowest number between 1 and 3, and the highest number between 1 and 3
4. The lowest number between 1 and 4, and the highest number between 1 and 4
5. The lowest number between 1 and 5, and the highest number between 1 and 5

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.

db<>fiddle