Home » SQL & PL/SQL » SQL & PL/SQL » Rank Signal
Rank Signal [message #401561] Tue, 05 May 2009 09:56 Go to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Hello,

I have a table TEST_DATA and want to add the field RANK (TEST_RESULT). The field RANK shall rank all records with SIGNAL = 1. The Rank is 1 if the previous record (order by ID) has been null. Does anyone know how to calculate RANK efficiently?

Thanks, Stefan


create table TEST_DATA
( ID number,
SIGNAL number
)

insert into TEST_DATA values ( 1, null);
insert into TEST_DATA values ( 2, 1);
insert into TEST_DATA values ( 3, 1);
insert into TEST_DATA values ( 4, 1);
insert into TEST_DATA values ( 5, 1);
insert into TEST_DATA values ( 6, 1);
insert into TEST_DATA values ( 7, 1);
insert into TEST_DATA values ( 8, 1);
insert into TEST_DATA values ( 9, 1);
insert into TEST_DATA values (10, 1);
insert into TEST_DATA values (11, null);
insert into TEST_DATA values (12, null);
insert into TEST_DATA values (13, null);
insert into TEST_DATA values (14, 1);
insert into TEST_DATA values (15, 1);
insert into TEST_DATA values (16, 1);
insert into TEST_DATA values (17, null);


create table TEST_RESULT
( ID number,
SIGNAL number,
RANK number
)

insert into TEST_RESULT values ( 1, null, null);
insert into TEST_RESULT values ( 2, 1, 1);
insert into TEST_RESULT values ( 3, 1, 2);
insert into TEST_RESULT values ( 4, 1, 3);
insert into TEST_RESULT values ( 5, 1, 4);
insert into TEST_RESULT values ( 6, 1, 5);
insert into TEST_RESULT values ( 7, 1, 6);
insert into TEST_RESULT values ( 8, 1, 7);
insert into TEST_RESULT values ( 9, 1, 8);
insert into TEST_RESULT values (10, 1, 9);
insert into TEST_RESULT values (11, null);
insert into TEST_RESULT values (12, null);
insert into TEST_RESULT values (13, null);
insert into TEST_RESULT values (14, 1, 1);
insert into TEST_RESULT values (15, 1, 2);
insert into TEST_RESULT values (16, 1, 3);
insert into TEST_RESULT values (17, null, null);
Re: Rank Signal [message #401572 is a reply to message #401561] Tue, 05 May 2009 10:20 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I explained you in details how to solve this kind of query in:
http://www.orafaq.com/forum/mv/msg/143004/395749/102589/#msg_395749

1/ You still don't want to follow guidelines
2/ You NEVER feedback to those who help you
3/ You don't show us that you learn something from what you explained you
4/ You don't show us that you try something

Conclusion... Guess!

Regards
Michel

[Updated on: Tue, 05 May 2009 10:21]

Report message to a moderator

Previous Topic: virtual table
Next Topic: Reference to Program that Fired the Trigger
Goto Forum:
  


Current Time: Thu Dec 08 00:06:12 CST 2016

Total time taken to generate the page: 0.17077 seconds