Home » SQL & PL/SQL » SQL & PL/SQL » Dense Rank - help needed
Dense Rank - help needed [message #265751] Fri, 07 September 2007 04:45 Go to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
Hi all!
Here is my environment:

CREATE TABLE LISTING(
  ID      NUMBER,
  FASE_ID NUMBER,
  PROG    NUMBER);

INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373265,9001,1);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373265,9001,2);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373265,9001,3);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373265,1001,4);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373265,1002,5);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373265,1002,6);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373265,1003,7);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373265,1003,8);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373265,1004,9);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373265,1004,10);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373265,1004,11);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373265,1004,12);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373265,1004,13);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373265,1010,14);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373265,1010,15);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373265,1011,16);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373265,1011,17);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373265,1012,18);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373265,1012,19);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373265,1013,20);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373265,1013,21);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373265,1014,22);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373265,1014,23);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,9001,24);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,9001,25);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,9001,26);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1001,27);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1001,28);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1002,29);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1002,30);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1003,31);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1003,32);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1004,33);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1004,34);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1005,35);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1005,36);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1006,37);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1006,38);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1007,39);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1007,40);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1008,41);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1008,42);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1009,43);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1009,44);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1010,45);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1010,46);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1011,47);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1011,48);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1012,49);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1012,50);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1013,51);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1013,52);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1001,53);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1001,54);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1001,55);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1002,56);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1002,57);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1003,58);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1003,59);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1004,60);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1004,61);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1005,62);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1005,63);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1006,64);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1006,65);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1007,66);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1007,67);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1008,68);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1008,69);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1009,70);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1009,71);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1010,72);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1010,73);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1011,74);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1011,75);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1012,76);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1012,77);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1013,78);
INSERT INTO LISTING(ID, FASE_ID, PROG) VALUES (373578,1013,79);


The result is:

SELECT ID, FASE_ID, PROG FROM LISTING ORDER BY PROG;

        ID    FASE_ID       PROG
---------- ---------- ----------
    373265       9001          1
    373265       9001          2
    373265       9001          3
    373265       1001          4
    373265       1002          5
    373265       1002          6
    373265       1003          7
    373265       1003          8
    373265       1004          9
    373265       1004         10
    373265       1004         11
    373265       1004         12
    373265       1004         13
    373265       1010         14
    373265       1010         15
    373265       1011         16
    373265       1011         17
    373265       1012         18
    373265       1012         19
    373265       1013         20
    373265       1013         21
    373265       1014         22
    373265       1014         23
    373578       9001         24
    373578       9001         25


My desired output:

        ID    FASE_ID       PROG         RK
---------- ---------- ---------- ----------
    373265       9001          1          1
    373265       9001          2          1
    373265       9001          3          1
    373265       1001          4          2
    373265       1002          5          3
    373265       1002          6          3
    373265       1003          7          4
    373265       1003          8          4
    373265       1004          9          5
    373265       1004         10          5
    373265       1004         11          5
    373265       1004         12          5
    373265       1004         13          5
    373265       1010         14          6
    373265       1010         15          6
    373265       1011         16          7
    373265       1011         17          7
    373265       1012         18          8
    373265       1012         19          8
    373265       1013         20          9
    373265       1013         21          9
    373265       1014         22         10
    373265       1014         23         10
    373578       9001         24         11
    373578       9001         25         11


I played with DENSE_RANK(), but I did not get anything useful...

SELECT ID, FASE_ID, PROG,
       DENSE_RANK() OVER (PARTITION BY ID, FASE_ID ORDER BY PROG) AS RK
  FROM LISTING
ORDER BY PROG


Any help would be very very appreciated!
Thaks a lot.
G.
Re: Dense Rank - help needed [message #265756 is a reply to message #265751] Fri, 07 September 2007 05:01 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
SELECT ID, FASE_ID, PROG,
       DENSE_RANK() OVER (ORDER BY ID DESC,FASE_ID DESC) AS RK
  FROM LISTING
ORDER BY PROG

[Updated on: Fri, 07 September 2007 05:09]

Report message to a moderator

Re: Dense Rank - help needed [message #265757 is a reply to message #265751] Fri, 07 September 2007 05:10 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
See more on DENSE Rank
http://download-uk.oracle.com/docs/cd/B14117_01/server.101/b10759/functions038.htm
Re: Dense Rank - help needed [message #265759 is a reply to message #265757] Fri, 07 September 2007 05:19 Go to previous messageGo to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
Hi, first off thanks for the suggestion.
I tried it, but I got the following output with the data I posted:

        ID    FASE_ID       PROG         RK
---------- ---------- ---------- ----------
    373265       9001          1         15
    373265       9001          2         15
    373265       9001          3         15
    373265       1001          4         24
    373265       1002          5         23
    373265       1002          6         23
    373265       1003          7         22
    373265       1003          8         22
    373265       1004          9         21
    373265       1004         10         21
    373265       1004         11         21
    373265       1004         12         21
    373265       1004         13         21
    373265       1010         14         20
    373265       1010         15         20
    373265       1011         16         19
    373265       1011         17         19
    373265       1012         18         18
    373265       1012         19         18
    373265       1013         20         17
    373265       1013         21         17
    373265       1014         22         16
    373265       1014         23         16
    373578       9001         24          1
    373578       9001         25          1
    373578       9001         26          1
    373578       1001         27         14
    373578       1001         28         14
    373578       1002         29         13
    373578       1002         30         13
    373578       1003         31         12
    373578       1003         32         12
    373578       1004         33         11
    373578       1004         34         11
    373578       1005         35         10
    373578       1005         36         10
    373578       1006         37          9
    373578       1006         38          9
    373578       1007         39          8
    373578       1007         40          8
    373578       1008         41          7
    373578       1008         42          7
    373578       1009         43          6
    373578       1009         44          6
    373578       1010         45          5
    373578       1010         46          5
    373578       1011         47          4
    373578       1011         48          4
    373578       1012         49          3
    373578       1012         50          3
    373578       1013         51          2
    373578       1013         52          2
    373578       1001         53         14
    373578       1001         54         14
    373578       1001         55         14
    373578       1002         56         13
    373578       1002         57         13
    373578       1003         58         12
    373578       1003         59         12
    373578       1004         60         11
    373578       1004         61         11
    373578       1005         62         10
    373578       1005         63         10
    373578       1006         64          9
    373578       1006         65          9
    373578       1007         66          8
    373578       1007         67          8
    373578       1008         68          7
    373578       1008         69          7
    373578       1009         70          6
    373578       1009         71          6
    373578       1010         72          5
    373578       1010         73          5
    373578       1011         74          4
    373578       1011         75          4
    373578       1012         76          3
    373578       1012         77          3
    373578       1013         78          2
    373578       1013         79          2


Am I missing something here? Embarassed

[Updated on: Fri, 07 September 2007 05:20]

Report message to a moderator

Re: Dense Rank - help needed [message #265764 is a reply to message #265751] Fri, 07 September 2007 05:26 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
try with this. Order wih be Ascending
SELECT ID, FASE_ID, PROG,
       DENSE_RANK() OVER (ORDER BY ID ASC,FASE_ID ASC) AS RK
  FROM LISTING
ORDER BY PROG
Re: Dense Rank - help needed [message #265770 is a reply to message #265764] Fri, 07 September 2007 05:56 Go to previous messageGo to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
...same as before, only with reversed ordering...

Sad
Re: Dense Rank - help needed [message #265774 is a reply to message #265770] Fri, 07 September 2007 06:15 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Quick and dirty:
SELECT ID
     , FASE_ID
     , PROG  
     , DENSE_RANK() OVER ( ORDER BY fv ) x
  FROM (  SELECT id
               , fase_id
               , prog
               , first_value(prog) OVER ( PARTITION BY id, fase_id 
                                          ORDER BY prog
                                        ) fv
          FROM   LISTING
          ORDER BY PROG       
       )
ORDER BY prog, id, fase_id
/
WARNING! Untested code.

MHE
Re: Dense Rank - help needed [message #265807 is a reply to message #265774] Fri, 07 September 2007 08:06 Go to previous message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
It was quick.
It was dirty.
But IT WAS WORKING! Smile

Thanks a lot!!!
Previous Topic: How to get login and hostname from the invoker of a procedure
Next Topic: Ora error ORA-00932
Goto Forum:
  


Current Time: Wed Dec 07 13:03:30 CST 2016

Total time taken to generate the page: 0.10193 seconds