OraFAQ Forum: SQL & PL/SQL » Sequential Numbering

Home » SQL & PL/SQL » SQL & PL/SQL » Sequential Numbering

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

Sequential Numbering [message #1578]

Tue, 14 May 2002 12:35

Susan Butts
Messages: 1
Registered: May 2002

Junior Member

We need help. I need to be able to have the database write a sequential number to a column based on an order by from another column. Example:

Country State Zip Members
US CA 72025 30
US TX 73041 51
US CA 75021 59
MX JL 3333 100

I will sort by Country and then by Members, then assign a sequential number to each of the rows within the country. Example of expected results:
RowCount Country State Zip Members
1 MX JL 3333 100
1 US CA 75021 59
2 US TX 73041 51
3 US CA 73025 30

How can I assign the RowCount as explained above.

Report message to a moderator

Re: Sequential Numbering [message #1580 is a reply to message #1578]

Tue, 14 May 2002 15:14

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

I wouldn't recommend actually storing this number in the database (What happens when you update the member counts? You have to update all the numbering.)

Instead, use analytical functions to generate the sequential number within country at query time.

sql>select row_number() over (partition by country order by members desc) rn,
  2         country, state, zip, members
  3    from t;
 
       RN CO ST ZIP     MEMBERS
--------- -- -- ----- ---------
        1 MX JL 3333        100
        1 US CA 75021        59
        2 US TX 73041        51
        3 US CA 72025        30

Report message to a moderator

Re: Sequential Numbering [message #1595 is a reply to message #1578]

Wed, 15 May 2002 08:31

Ramon Gallego
Messages: 1
Registered: May 2002

Junior Member

Todd,
first of all Thanks for your help...
We got an error after trying your suggestion. can you tell us what we are doing wrong...

here is our code..

SQL> select * from tc;

COUNT ST ZIP MEMBERS
----- -- ----- ---------
US CA 75025 300
US CA 73041 562
US MO 25254 600
US MO 25225 351
US MO 25222 700
US TX 75555 300
US TX 32500 300
US CA 73051 800

8 rows selected.

SQL> select row_number() over (partition by country order by members desc) rn,
2 country, state, zip, members
3 from tc;
select row_number() over (partition by country order by members desc) rn,
*
ERROR at line 1:
ORA-00919: invalid function

Report message to a moderator