Personal tools
You are here: Home DB2 How To's How to rank rows on DB2
Navigation
Log in


Forgot your password?
 
Document Actions

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

Security Awareness
Would you like your company to implement gamification into your security awareness program?





Polls