February 21, 2018 by Kenneth Fisher
If you’ve never worked with windowing functions they look something like this:
SELECT name, max(create_date) OVER -- No this isn't meant to make sense (PARTITION BY lower(left(name,1)) ORDER BY owner_sid) FROM sys.databases;
The other day someone mentioned that you could use ROW_NUMBER which requires the OVER clause without either the PARTITION BY or the ORDER BY parts. So let’s try that out.
-- Create a table to test with CREATE TABLE Windowing (col1 int, col2 int); INSERT INTO Windowing VALUES (1,1), (1,2), (1,3), (1,4), (1,5) ,(2,6), (2,7), (2,8), (2,9), (2,10); GO
-- Test ROW_NUMBER without PARTITION BY or ORDER BY SELECT ROW_NUMBER() OVER (), * FROM Windowing;
The function ‘ROW_NUMBER’ must have an OVER clause with ORDER BY.
So nope, doesn’t work. But can we get around it?
SELECT ROW_NUMBER() OVER (ORDER BY 1), * FROM Windowing; GO SELECT ROW_NUMBER() OVER (ORDER BY '1'), * FROM Windowing; GO SELECT ROW_NUMBER() OVER (ORDER BY DB_ID()), * FROM Windowing; GO
Ok, so you can do it, but it takes some work. No integers, no constants at all, but a function that returns a constant value will do it. Not really sure why you would want to do this, since generally in order for a row number to be useful its done in order of some value. On the other hand, if you absolutely NEED a unique number value per row, don’t have a useful indexed column (a staging table that’s a heap maybe?), and you don’t want to pay for an expensive sort, this could be handy.
Let’s try one more for fun. MAX
SELECT col1, MAX(col2) OVER () FROM Windowing;
Now that works! However, now it doesn’t work with a GROUP BY making it less useful.
SELECT col1, MAX(col2) OVER () FROM Windowing GROUP BY col1;
Column ‘Windowing.col2’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Unlike a simple MAX of course.
SELECT col1, MAX(col2) FROM Windowing GROUP BY col1;