1# 2# Try DENSE_RANK() function 3# 4create table t1 ( 5pk int primary key, 6a int, 7b int 8); 9insert into t1 values 10( 1 , 0, 10), 11( 2 , 0, 10), 12( 3 , 1, 10), 13( 4 , 1, 10), 14( 8 , 2, 10), 15( 5 , 2, 20), 16( 6 , 2, 20), 17( 7 , 2, 20), 18( 9 , 4, 20), 19(10 , 4, 20); 20select a, rank() over (order by a) as rank, 21dense_rank() over (order by a) as dense_rank 22from t1; 23a rank dense_rank 240 1 1 250 1 1 261 3 2 271 3 2 282 5 3 292 5 3 302 5 3 312 5 3 324 9 4 334 9 4 34select a, b, rank() over (partition by b order by a) as rank, 35dense_rank() over (partition by b order by a) as dense_rank 36from t1; 37a b rank dense_rank 380 10 1 1 390 10 1 1 401 10 3 2 411 10 3 2 422 10 5 3 432 20 1 1 442 20 1 1 452 20 1 1 464 20 4 2 474 20 4 2 48drop table t1; 49# 50# Test with null values in the table. 51# 52create table t2 (s1 int, s2 char(5)); 53insert into t2 values (1,'a'); 54insert into t2 values (null,null); 55insert into t2 values (1,null); 56insert into t2 values (null,'a'); 57insert into t2 values (null,'c'); 58insert into t2 values (2,'b'); 59insert into t2 values (-1,''); 60select *, rank() over (order by s1) as rank, 61dense_rank() over (order by s1) as dense_rank 62from t2 63order by s1, s2; 64s1 s2 rank dense_rank 65NULL NULL 1 1 66NULL a 1 1 67NULL c 1 1 68-1 4 2 691 NULL 5 3 701 a 5 3 712 b 7 4 72select *, rank() over (partition by s2 order by s1) as rank, 73dense_rank() over (partition by s2 order by s1) as dense_rank 74from t2 75order by s1, s2; 76s1 s2 rank dense_rank 77NULL NULL 1 1 78NULL a 1 1 79NULL c 1 1 80-1 1 1 811 NULL 2 2 821 a 2 2 832 b 1 1 84select *, rank() over (order by s2) as rank, 85dense_rank() over (order by s2) as dense_rank 86from t2 87order by s2, s1; 88s1 s2 rank dense_rank 89NULL NULL 1 1 901 NULL 1 1 91-1 3 2 92NULL a 4 3 931 a 4 3 942 b 6 4 95NULL c 7 5 96select *, rank() over (partition by s1 order by s2) as rank, 97dense_rank() over (partition by s1 order by s2) as dense_rank 98from t2; 99s1 s2 rank dense_rank 100NULL NULL 1 1 101NULL a 2 2 102NULL c 3 3 103-1 1 1 1041 NULL 1 1 1051 a 2 2 1062 b 1 1 107drop table t2; 108