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)
*/