Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Ranking based on a Group?

433185Jun 29 2011 — edited Jun 29 2011
Hello friends

I have a table t
CREATE TABLE T
(ID NUMBER(4),  
NAME VARCHAR2(40))

INSERT INTO T VALUES (1,'JAMES');
INSERT INTO T VALUES (1,'DOLLY');
INSERT INTO T VALUES (2,'MICHEAL');
INSERT INTO T VALUES (2,'FLASH');
INSERT INTO T VALUES (3,'JAMES');
INSERT INTO T VALUES (3,'MARY');
INSERT INTO T VALUES (4,'JAMES');
INSERT INTO T VALUES (4,'DOLLY');
INSERT INTO T VALUES (5,'JAMES');
INSERT INTO T VALUES (5,'DOLLY');
INSERT INTO T VALUES (6,'JAMES');
INSERT INTO T VALUES (6,'MARY');

SELECT * FROM T ORDER BY 1
/

ID   NAME                    
1    JAMES                    
1    DOLLY                     
2    MICHEAL                   
2    FLASH                      
3    JAMES
3    MARY
4    JAMES
4    DOLLY
5    JAMES
5    DOLLY
6    JAMES
6    MARY

each 'ID' has two values always.
I want to rank the data based on same pair 'name' in an 'ID'

for example, my desired output is:

ID   NAME                    RANK
1    JAMES                   1 
1    DOLLY                   1
2    MICHEAL                1   
2    FLASH                    1 
3    JAMES                    1
3    MARY                     1
4    JAMES                    2  ---> THAT IS RANK 2 BECAUSE THIS IS THE 2ND TIME JAMES AND DOLLY ARE IN THE SAME 'ID' 
4    DOLLY                    2  -----> SAME AS ABOVE
5    JAMES                    3 ---> THAT IS RANK 2 BECAUSE THIS IS THE 3RD TIME JAMES AND DOLLY ARE IN THE SAME 'ID' 
5    DOLLY                    3  -----> SAME AS ABOVE
6    JAMES                    2 ---> THAT IS RANK 2 BECAUSE THIS IS THE 2ND TIME JAMES AND MARY ARE IN THE SAME 'ID' 
6    MARY                     2 -----> SAME AS ABOVE
I hope I have been able to clear my question.
I want the output in exactly above format. I will appreciate the smallest possible query to do this since I am to use this in a subquery.

Awaiting your kind replies.
regards
Hamza

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 27 2011
Added on Jun 29 2011
3 comments
575 views