OraFAQ Forum: SQL & PL/SQL » Any SQL GURU -- Interesting SQL Problem

Home » SQL & PL/SQL » SQL & PL/SQL » Any SQL GURU -- Interesting SQL Problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Any SQL GURU -- Interesting SQL Problem [message #20162]

Thu, 02 May 2002 13:46

sql gurus
Messages: 22
Registered: May 2002

Junior Member

I have a coulmn which has repeated values. i want to write a sql to number the values from 1..n for each value.
Ex.
Column A
-------
10
10
10
40
30
30
30

the result should be
Column A Number
------- -------
10 1
10 2
10 3
40 1
30 1
30 2
30 3

Any thoughts appreciated.

Report message to a moderator

Re: Any SQL GURU -- Interesting SQL Problem [message #20164 is a reply to message #20162]

Thu, 02 May 2002 14:38

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Jay, if there's a chance you don't really need these numbers to be persistant, you can just assign them on the fly using analytical functions (assuming you are 8i or later):

select c, row_number() over (partition by c order by c) rn 
  from t;

If the row numbers absolutely have to be stored, then you can:

update t
   set s = (select rn
              from (select rowid, c, row_number() over (partition by c order by c) rn
                      from t) t2
             where t2.c = t.c
               and t2.rowid = t.rowid);

where s is the name of the "numbering" column.

If you are not on 8i, then you will probably need to use a PL/SQL routine to loop through the table and do the numbering assignments.

Report message to a moderator