Home » SQL & PL/SQL » SQL & PL/SQL » Dense Rank - help needed
Dense Rank - help needed [message #265751] |
Fri, 07 September 2007 04:45  |
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 #265759 is a reply to message #265757] |
Fri, 07 September 2007 05:19   |
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?
[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   |
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 #265774 is a reply to message #265770] |
Fri, 07 September 2007 06:15   |
 |
Maaher
Messages: 7065 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
|
|
|
|
Goto Forum:
Current Time: Sun Feb 16 06:49:22 CST 2025
|