Showing Rank in SQL Record set

Sometimes you want to show the ranking of the record in the results of your SQL SELECT statement.  How do you do that in DB2? Here’s how:

If you are on an System i (aka AS/400, iSeries), there is a nice little table that is great for trying out SQL tricks.  Everybody has it: QIWS/QCUSTCDT.

SELECT RANK() OVER (ORDER BY LSTNAM), A.*
   FROM QIWS/QCUSTCDT A

This will show all a derived field called “RANK”, and then all the fields in the table.  Since you have sorted the data by LSTNAM and you created the rank over the same field, the first record in your result set will have a rank of 1.  The second will have a rank of  2, etc.

But the really interesting thing is that the rank doesn’t need to be over the field that you order the results.  For example, if you wanted to rank the records by who had the largest balance due, but still sort the list by last name you could do this:

SELECT RANK() OVER (ORDER BY BALDUE DESC), A.*
   FROM QIWS/QCUSTCDT A
   ORDER BY LSTNAM

Does this help?  Leave a comment.