ORDER BY clause in a Window function with a frame clause

February 9, 2023

I want to take the min and max for a column within each partition.
See example below (both methods give the correct answer). I do not understand why I have to add the ORDER BY clause.
When using min and max as the aggregate function what possible difference will the ORDER BY have?

DROP TABLE IF EXISTS #HELLO;
CREATE TABLE #HELLO (Category char(2), q int);
INSERT INTO #HELLO (Category, q)
VALUES ('A',1), ('A',5), ('A',6), ('B',0), ('B',3)

SELECT *, 
     min(q) OVER (PARTITION BY category ORDER BY (SELECT NULL) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS minvalue
    ,max(q) OVER (PARTITION BY category ORDER BY (SELECT NULL) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS maxvalue
    ,min(q) OVER (PARTITION BY category ORDER BY q ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS minvalue2
    ,max(q) OVER (PARTITION BY category ORDER BY q ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS maxvalue2
FROM #HELLO;

>Solution :

If you use the ROWS or RANGE clause in a OVER clause then you need to provide an ORDER BY clause, because you are typically telling the OVER clause how many rows to look behind and forward, which can only be determined if you have an ‘ORDER BY’.

However in your case because you use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING i.e. all rows, you don’t need any of it. The following produces the same results:

SELECT *, 
     min(q) OVER (PARTITION BY category) AS minvalue
    ,max(q) OVER (PARTITION BY category) AS maxvalue
    ,min(q) OVER (PARTITION BY category) AS minvalue2
    ,max(q) OVER (PARTITION BY category) AS maxvalue2
FROM #HELLO;