T-SQL 2005 Enhancements - Ranking - ROWNUMBER()

There are four new ranking functions that have been added to SQL Server 2005.  Today I want to touch on the first of these.  ROWNUMBER.  This is a very useful function, but it has the worst name since my parents thought that Louis would be a nice name, forgetting that other children would find it ultra goofy.  It gives you a unique value per row (or within a group), but it is actually a row position based on a particular ordering. 

The way it works is:

SELECT ROW_NUMBER() OVER (ORDER BY <columns>)
FROM   <tablename>

You specify an ordering and then it assigns a number of each row.  If the ordering is not unique, the values will be randomly assigned.  Be careful, because if you don't apply an order by clause to this query, it will seem to come out sorted.  On a single processor test system it will probably always be that way, but it is simply sorted because it is easier for the optimizer to because it has to sort to do the ROW_NUMBER function.  But it is not required.  You can have multiple ROW_NUMBER functions per output.  For example, consider we have the following table of rows (create script at the end).

value       value2
----------- -----------
3             5
1             2
2             1
1             1
3             4
2             2
3             1
3             2
3             6
3             3

Sort it and you will see it is 1,1 - 1,2, 2,1 - 2,2 and 3,1 to 3,6.  I intentionally did not sort it, nor index it, just for demo purposes.

select value,value2,
    ( select count(*)
      from ranking as r1
      where r1.value < ranking.value
         or (r1.value = ranking.value
              and r1.value2 < ranking.value2) )+ 1 as rowNumber
from ranking
order by value, value2

This returned:

value       value2      rowNumber
----------- ----------- -----------
1             1             1
1             2             2
2             1             3
2             2             4
3             1             5
3             2             6
3             3             7
3             4             8
3             5             9
3             6            10

In 2005, it is much easier:

select value, value2,
  row_number() over ( order by value,value2) as ROW_NUMBER
from ranking
order by value, value2

value       value2      ROW_NUMBER
----------- ----------- --------------------
1             1             1
1             2             2
2             1             3
2             2             4
3             1             5
3             2             6
3             3             7
3             4             8
3             5             9
3             6             10

But what if the ORDER BY for the query was not the same as the ORDER By for the ROW_NUMBER function?  Not a problem:

select value, value2,
  row_number() over ( order by value,value2) as ROW_NUMBER
from ranking
order by value2

Returns:

value       value2      ROW_NUMBER
----------- ----------- --------------------
1             1             1
2             1             3
3             1             5
3             2             6
2             2             4
1             2             2
3             3             7
3             4             8
3             5             9
3             6            10

This is why you have to be really careful that this is not taken literally as the row number of the output.  Now you can have more than one order criteria and with 2 row numbers:

select value, value2,
  row_number() over ( order by value,value2) as ROW_NUMBER,
  row_number() over ( order by value2) as ROW_NUMBER2
from ranking

returns:

value       value2      ROW_NUMBER     ROW_NUMBER2
----------- ----------- -------------------- --------------------
1             1             1                        1
2             1             3                        2
3             1             5                        3
3             2             6                        4
2             2             4                        5
1             2             2                        6
3             3             7                        7
3             4             8                        8
3             5             9                        9
3             6            10                       10

Finally, you can add additional grouping information, and group within sets using the PARTITION keyword:

SELECT ROW_NUMBER() OVER (PARTITION BY <columns> ORDER BY <columns>)
FROM   <tablename>

For example:

select value, value2,
  row_number() over (partition by value order by value2) as ROW_NUMBER
from ranking
order by value

value       value2      ROW_NUMBER
----------- ----------- --------------------
1             1             1
1             2             2
2             1             1
2             2             2
3             1             1
3             2             2
3             3             3
3             4             4
3             5             5
3             6             6

Is this useful?  It was my first feature blog for a reason.  I was working on a big renormalization project just yesterday and had to do some of this ordering stuff by hand in SQL Server 2000, and it was so hard to do that I gave up and wrote a cursor (it was a single time project, so it was just faster!)  Next time, we will talk about the another ranking functions, RANK, DENSE_RANK, and/or NTILE.

Table create
------------------

drop table ranking
go
create table ranking
(
   value int,
   value2 int
)

insert into ranking
values (3,5)
insert into ranking
values (1,2)
insert into ranking
values (2,1)
insert into ranking
values (1,1)
insert into ranking
values (3,4)
insert into ranking
values (2,2)
insert into ranking
values (3,1)
insert into ranking
values (3,2)
insert into ranking
values (3,6)
insert into ranking
values (3,3)