Home » SQL & PL/SQL » SQL & PL/SQL » Need help in sequence.
Need help in sequence. [message #338371] Mon, 04 August 2008 14:43 Go to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Good afternoon.

I need a new sequence number to be started for each change in row. I have a table like this:

A 1
A 2
A 3
C 1
C 3
C 6

I need the output like this:

A 1 1
A 2 2
A 3 3
C 1 1
C 3 2
C 6 3

Can anybody help me out in this. I would appreciate that.
Thanks much.
LN
Re: Need help in sequence. [message #338373 is a reply to message #338371] Mon, 04 August 2008 15:02 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Do you need it in the table or just the output?

Look at analytics such as row_number, over and partition.
Re: Need help in sequence. [message #338400 is a reply to message #338373] Mon, 04 August 2008 18:09 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
I need it in the table. I have to update the third column with the sequence. Is there any way or example how to do it, Please show me. I would appreciate that.

Thanks.
Re: Need help in sequence. [message #338426 is a reply to message #338400] Tue, 05 August 2008 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use update and row_number.

Regards
Michel
Re: Need help in sequence. [message #338608 is a reply to message #338371] Tue, 05 August 2008 07:51 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Michel.

Thanks much for your help.
I tried to update with row_num but get the error message
" ORA-00936: missing expression "

Please help me. Here is my update statement:

UPDATE TableA
SET Col3 = subquery.Upd
       (SELECT Col1, Col2,
        ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col2) as Upd
        FROM TableA) subquery
INNER JOIN TableA ON
subquery.Col1 = TableA.Col1
and subquery.Col2 = TableA.Col2;



Thank you very much.
LN
Re: Need help in sequence. [message #338613 is a reply to message #338608] Tue, 05 August 2008 08:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
(Restricted) Syntax is:
update <table>
set <col>=(<subquery>)
[where <condition>]

Regards
Michel

[Updated on: Tue, 05 August 2008 08:01]

Report message to a moderator

Re: Need help in sequence. [message #338647 is a reply to message #338371] Tue, 05 August 2008 09:41 Go to previous message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Michel.

Thank you very much. I appreciated for your help.

LN
Previous Topic: Where Clause in sql
Next Topic: Transaction Logging
Goto Forum:
  


Current Time: Fri Dec 02 18:28:30 CST 2016

Total time taken to generate the page: 0.34951 seconds