Max() and Min() have different behaviours?

Please help me through my confusion.

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

>Solution :

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:

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

This would result in the results:

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 make the "most" sense here, as getting the MIN/MAX in order doesn’t really make sense.

db<>fiddle