Thursday, June 30, 2011

Learn Rank(), Dense_rank() and Row_Number() functions by example



use testdb;
go
create table #test (dim char(1), v int);
insert into #test values('a',1);
insert into #test values('a',2);
insert into #test values('a',3);
insert into #test values('a',4);
insert into #test values('b',1);
insert into #test values('b',2);
insert into #test values('c',1);
go
select dim, v, ROW_NUMBER() over (order by v) as 'RowNumber'
from #test;

/*
dim v RowNumber
---- ----------- --------------------
a 1 1
b 1 2
c 1 3
b 2 4
a 2 5
a 3 6
a 4 7

(7 row(s) affected)
*/

select dim,v,ROW_NUMBER() over (partition by dim order by v desc) as 'RowNumber'
from #test;

/*
dim v RowNumber
---- ----------- --------------------
a 4 1
a 3 2
a 2 3
a 1 4
b 2 1
b 1 2
c 1 1

(7 row(s) affected)
*/

select dim,v,RANK() over (order by v) 'Rank'
from #test;

/*
dim v Rank
---- ----------- --------------------
a 1 1
b 1 1
c 1 1
b 2 4
a 2 4
a 3 6
a 4 7

(7 row(s) affected)
*/


select dim,v,RANK() over (partition by dim order by v) 'Rank'
from #test;

/*
dim v Rank
---- ----------- --------------------
a 1 1
a 2 2
a 3 3
a 4 4
b 1 1
b 2 2
c 1 1

(7 row(s) affected)

*/

select dim, v, DENSE_RANK() over (order by v) 'DenseRank'
from #test;

/*
dim v DenseRank
---- ----------- --------------------
a 1 1
b 1 1
c 1 1
b 2 2
a 2 2
a 3 3
a 4 4

(7 row(s) affected)
*/

select dim,v,NTILE(4) over (order by v) as 'QuarterGroup'
from #test;
/*
dim v QuarterGroup
---- ----------- --------------------
a 1 1
b 1 1
c 1 2
b 2 2
a 2 3
a 3 3
a 4 4

(7 row(s) affected)
*/