Home » SQL & PL/SQL » SQL & PL/SQL » Need help on SQL statement ..
Need help on SQL statement .. [message #282067] Tue, 20 November 2007 14:17 Go to next message
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 #282070 is a reply to message #282067] Tue, 20 November 2007 14:50 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
What possible reason could you have for storing a sequence number, when it would be so easy to generate when you pull the data from the file.

select c1,c2,c3,
ROW_NUMBER() OVER (ORDER BY c3,c1) AS c4
from test_data
order by c3,c1;

[Updated on: Tue, 20 November 2007 14:51]

Report message to a moderator

Re: Need help on SQL statement .. [message #282072 is a reply to message #282070] Tue, 20 November 2007 14:57 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Bill B
Messages: 1482
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 Go to previous messageGo to next message
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 #282078 is a reply to message #282076] Tue, 20 November 2007 15:39 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Partition by C3 not order by c3
Re: Need help on SQL statement .. [message #282083 is a reply to message #282078] Tue, 20 November 2007 16:38 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.

Re: Need help on SQL statement .. [message #282121 is a reply to message #282087] Tue, 20 November 2007 23:25 Go to previous message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
you can give a try.

regards,

[Updated on: Tue, 20 November 2007 23:28]

Report message to a moderator

Previous Topic: Disable Constraints from Parent and Child tables
Next Topic: want to update a column of complex table
Goto Forum:
  


Current Time: Sun Dec 04 00:09:48 CST 2016

Total time taken to generate the page: 0.14068 seconds