Incrementing Counts in SQL March 25, 2007
Posted by Duncan in Oracle, SQL.trackback
Fairly frequently (particularly during Data Migration when deriving new key values) you have to rank and number rows within a particular key value. For instance, Key1 may have three rows – and these should be given a value of 1, 2 & 3. Key 2 however may have a different number of values but these should start again from 1. Example shown in this table:
| Value | RowNum |
| Key1 | 1 |
| Key1 | 2 |
| Key1 | 3 |
| Key2 | 1 |
| Key3 | 1 |
| Key3 | 2 |
| Key3 | 3 |
| Key3 | 4 |
The Rownum field can be achieved in Oracle using the following SQL:
SELECT <Key1>
, rank() over (partition by <Key1> order by <OrderField> )
FROM <recname> k
As well as the RANK() function, there is a DENSE_RANK() function. The latter doesn’t leave gaps when there is a tie, for example RANK may give the output 1,2,2,4 where DENSE_RANK would give 1,2,2,3. The highest rank value provided by a DENSE_RANK statement would be the number of unique values.
Comments
Sorry comments are closed for this entry
The PeopleSoft Tipster Blog 
Thank you for this info
( : >s are interpreted in the source tag :()
Good spot. I’ve changed the code to correct that now. Thanks!