DB2 結果に順位をふる

DB2 結果に順位をふる

[DB2][Database][SQL]

RANK()

• 結果に順位をふる

select salary, rank() over(order by salary desc) as rank from employee
order by salary desc

結果

SALARY      RANK
---------   ----
152750.00     1
 98250.00     2
 96170.00     3
 94250.00     4
 89750.00     5
 86150.00     6
 80175.00     7
 73800.00     8
 72250.00     9
 69840.00    10
 68420.00    11
 68420.00    11
 68270.00    13
 66500.00    14
 64680.00    15
 62250.00    16
 57740.00    17
 55280.00    18
 51340.00    19
 50450.00    20
 49840.00    21
 49250.00    22
 49180.00    23
 48760.00    24
 47250.00    25
 46500.00    26
 46250.00    27
 45370.00    28
 44680.00    29
 43840.00    30
 42180.00    31
 39950.00    32
 39250.00    33
 37760.00    34
 37750.00    35
 37380.00    36
 36250.00    37
 35900.00    38
 35900.00    38
 35370.00    40
 35340.00    41
 31840.00    42

DENSE_RANK()

• 結果に順位をふるのは、RANK()と同じだが、順位をスキップしない

select salary, dense_rank() over(order by salary desc) as rank from employee
order by salary desc

結果

SALARY      RANK
---------   ----
152750.00     1
 98250.00     2
 96170.00     3
 94250.00     4
 89750.00     5
 86150.00     6
 80175.00     7
 73800.00     8
 72250.00     9
 69840.00    10
 68420.00    11
 68420.00    11
 68270.00    12
 66500.00    13
 64680.00    14
 62250.00    15
 57740.00    16
 55280.00    17
 51340.00    18
 50450.00    19
 49840.00    20
 49250.00    21
 49180.00    22
 48760.00    23
 47250.00    24
 46500.00    25
 46250.00    26
 45370.00    27
 44680.00    28
 43840.00    29
 42180.00    30
 39950.00    31
 39250.00    32
 37760.00    33
 37750.00    34
 37380.00    35
 36250.00    36
 35900.00    37
 35900.00    37
 35370.00    38
 35340.00    39
 31840.00    40