How to rank rows on DB2
use the functions rank(), dense_rank(), rownumber()
If you have a TABLEX with the following content:
COD
ab
dd
ac
ddae
fa
To rank these data, perform the command below:
db2 "SELECT RANK() OVER (ORDER BY COD) AS RANK,COD FROM TABLEX"
Results:
RANK COD
--------- ----------
1 ab
2 ac
3 ae
4 dd
4 dd
6 fa
To dense rank, perform the command below:
db2 "SELECT DENSE_RANK() OVER (ORDER BY COD) AS RANK,COD FROM TABLEX"
RANK COD
--------- ----------
1 ab
2 ac
3 ae
4 dd
4 dd
5 fa
To put row number in your data output, perform the command below:
db2 "SELECT ROWNUMBER() OVER (ORDER BY COD) AS RANK,COD FROM TABLEX"
RANK COD
--------- ----------
1 ab
2 ac
3 ae
4 dd
5 dd
6 fa