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
dd ae
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