Incrementing Counts in SQL

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:

ValueRowNumKey11Key12Key13Key21Key31Key32Key33Key34

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.

Like this:

Like

Be the first to like this post.

Comments»

1. Glide -May 19, 2008

Thank you for this info
( : >s are interpreted in the source tag )

Reply

2. PeopleSoft Tipster -May 19, 2008

Good spot. I’ve changed the code to correct that now. Thanks!

Reply