Need help on SQL statement .. [message #282067] |
Tue, 20 November 2007 14:17  |
frank.svs
Messages: 162 Registered: February 2006
|
Senior Member |
|
|
Hi pals,
I need small help.
I have a table with data as follows.
select * from test_data
c1 c2 c3
----------------------
111 4 101
122 3 101
133 2 102
144 2 103
155 1 103
Now i need to generate a new column c4 as sequence number in the output
I need to get the output something as follows
c4 c1 c2 c3
-----------------------------
1 111 4 101
2 122 3 101
1 133 2 102
1 144 2 103
2 155 1 103
The newly generated column contains sequence numbers starting from 1 and the sequence should be resetted again to 1 whenever a new c3 value is encountered(for example when the c3 value changes from 101 to 102 the sequence should be resetted to 1).
Can anyone help me out on this regard.
Thanks in advance,
franky
|
|
|
|
Re: Need help on SQL statement .. [message #282072 is a reply to message #282070] |
Tue, 20 November 2007 14:57   |
frank.svs
Messages: 162 Registered: February 2006
|
Senior Member |
|
|
If am executing the query this is the output i am getting. But this is not my desired output. The sequence no or rownum should be resetted to 1 whenever it encounters a new C3 value.
This is one of the requirement from my client.
SQL> select c1,c2,c3,
2 ROW_NUMBER() OVER (ORDER BY c3,c1) AS c4
3 from test_data;
C1 C2 C3 C4
---------- ---------- ---------- ----------
111 4 101 1
122 3 101 2
133 2 102 3
144 2 103 4
155 1 103 5
SQL>
Any other solution for this?
|
|
|
Re: Need help on SQL statement .. [message #282073 is a reply to message #282072] |
Tue, 20 November 2007 14:59   |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
frank.svs wrote on Tue, 20 November 2007 14:57 | If am executing the query this is the output i am getting. But this is not my desired output. The sequence no or rownum should be resetted to 1 whenever it encounters a new C3 value.
This is one of the requirement from my client.
SQL> select c1,c2,c3,
2 ROW_NUMBER() OVER (ORDER BY c3,c1) AS c4
3 from test_data;
C1 C2 C3 C4
---------- ---------- ---------- ----------
111 4 101 1
122 3 101 2
133 2 102 3
144 2 103 4
155 1 103 5
SQL>
Any other solution for this?
|
Sorry, I made a typo. Try the following.
select c1,c2,c3,
ROW_NUMBER() OVER (ORDER BY c3) AS c4
from test_data
order by c3,c1;
|
|
|
Re: Need help on SQL statement .. [message #282076 is a reply to message #282073] |
Tue, 20 November 2007 15:31   |
frank.svs
Messages: 162 Registered: February 2006
|
Senior Member |
|
|
Even then i am getting the same output.
SQL> select c1,c2,c3,
2 ROW_NUMBER() OVER (ORDER BY c3) AS c4
3 from test_data
4 order by c3,c1;
C1 C2 C3 C4
---------- ---------- ---------- ----------
111 4 101 1
122 3 101 2
133 2 102 3
144 2 103 4
155 1 103 5
|
|
|
|
Re: Need help on SQL statement .. [message #282083 is a reply to message #282078] |
Tue, 20 November 2007 16:38   |
frank.svs
Messages: 162 Registered: February 2006
|
Senior Member |
|
|
Finally we got it.
SQL> ed
Wrote file afiedt.buf
1 select c1,c2,c3,
2 ROW_NUMBER() OVER (partition BY c3 order by c3) AS c4
3 from test_data
4* order by c3,c1
SQL> /
C1 C2 C3 C4
---------- ---------- ---------- ----------
111 4 101 1
122 3 101 2
133 2 102 1
144 2 103 1
155 1 103 2
SQL>
Thank you very much.
|
|
|
Re: Need help on SQL statement .. [message #282087 is a reply to message #282083] |
Tue, 20 November 2007 17:19   |
frank.svs
Messages: 162 Registered: February 2006
|
Senior Member |
|
|
One more small requirement to be fulfilled.
While generating the row_number() itseld i.e the sequence nos i need to update the assignment_number column value by taking the seq_no generated for each row .
111 4 101 1
122 3 101 2
133 2 102 1
144 2 103 1
155 1 103 2
finally, My test table should contain the data as follows
1 4 101
2 3 101
1 2 102
1 2 103
2 1 103
How can we do it?
Thanks.
|
|
|
|