jump to navigation

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.

Advertisements

Comments

1. Glide - May 19, 2008

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

2. PeopleSoft Tipster - May 19, 2008

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


Sorry comments are closed for this entry

%d bloggers like this: