SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Windowing without a partition by or an order by

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;
Msg 4112, Level 15, State 1, Line 16
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;
Msg 8120, Level 16, State 1, Line 29
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;

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...