Home » SQL & PL/SQL » SQL & PL/SQL » Sequencing help
Sequencing help [message #288073] Fri, 14 December 2007 12:17 Go to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
Hey simple question here. I have a table with a column that has an unspecified number of duplicates, basically I want to number each one with in increasing sequence. For instance

Column_with_duplicates Column I want to add
Bob 1
Bob 2
Bob 3
Susan 1
Susan 2
Joe 1
Joe 2
Joe 3
Joe 4

Can anyone give me a tip on how to do this? Thanks!
Re: Sequencing help [message #288077 is a reply to message #288073] Fri, 14 December 2007 13:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try row_number function.

Regards
Michel
Re: Sequencing help [message #288081 is a reply to message #288073] Fri, 14 December 2007 13:18 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
Perfect, that is exactly what I needed.

[Updated on: Fri, 14 December 2007 13:19]

Report message to a moderator

Re: Sequencing help [message #288082 is a reply to message #288073] Fri, 14 December 2007 13:23 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
tmcallister,
It might be helpful for those who come after you if you actually shared the solution by posting it here.

TIA
Re: Sequencing help [message #288084 is a reply to message #288081] Fri, 14 December 2007 13:26 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
Except that doesn't work in an update statement ... blah ... guess I'll just two step it.

Unless there is something else?

Thanks again.
Re: Sequencing help [message #288085 is a reply to message #288084] Fri, 14 December 2007 13:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read update query problem topic.

Regards
Michel
Re: Sequencing help [message #288086 is a reply to message #288073] Fri, 14 December 2007 13:37 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
>Except that doesn't work in an update statement ... blah ... guess I'll just two step it.
"doesn't work" is less than helpful & illuminating

>Unless there is something else?
We are not standing behind you & only know what you post here.

We know you have a problem.
My crystal ball is in the shop for repair so You're On Your Own (YOYO); unless & until you provide some specifics.

please read & FOLLOW posting guidelines as stated in URL below:
http://www.orafaq.com/forum/t/88153/0/
Re: Sequencing help [message #288087 is a reply to message #288086] Fri, 14 December 2007 13:56 Go to previous message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
My apologizes, I'm working with a rather complex well database of several million rows. I did't want to get into all the complexities of my system so I distilled the crux of my problem into a rather trivial example.

So background on my trivial example:

Setup:

create table child
(
name varchar2(25)
);

insert into child values ('Bob');
insert into child values ('Bob');
insert into child values ('Bob');
insert into child values ('Susan');
insert into child values ('Susan');
insert into child values ('Joe');
insert into child values ('Joe');
insert into child values ('Joe');
insert into child values ('Joe');

The answer to my question:

select name, row_number() over (partition by name order by name) from child

My new problem, I want to add this column to the table:

alter table child add
(
observation_number varchar2(8)
);

update child a
set observation_number = (select obs_no from (select name, row_number() over (partition by name order by name) obs_no from child) b
where a.rowid = b.rowid)

Thanks for being my mind reader Michel Cadot



[Updated on: Fri, 14 December 2007 14:04]

Report message to a moderator

Previous Topic: how to select a non-group by value from a group by clause
Next Topic: rows into columns and columns into rows
Goto Forum:
  


Current Time: Fri Dec 02 12:09:12 CST 2016

Total time taken to generate the page: 0.11533 seconds