All Forums Database
ihtisham20 4 posts Joined 06/09
13 Aug 2009
Ranking data to get same value

I have following information which i want to rankName Number Status DateDave 234 A 2/2/2009Dave 234 A 3/2/2009Dave 234 A 4/2/2009Dave 234 A 4/2/2009Dave 234 B 5/2/2009Dave 234 A 6/2/2009Dave 234 A 7/2/2009Dave 234 A 7/2/2009I WANT RANK TO RESULT LIKE THISName Number Status Date RANKDave 234 A 2/2/2009 1Dave 234 A 3/2/2009 1Dave 234 A 4/2/2009 1Dave 234 A 4/2/2009 1Dave 234 B 5/2/2009 2Dave 234 A 6/2/2009 3Dave 234 A 7/2/2009 3Dave 234 A 7/2/2009 3I understand rank with partation by name and status and order by date.......... But it doesn't output the desired RANK output. Do you know any trick on thiss?

CarlosAL 512 posts Joined 04/08
14 Aug 2009

Not sure about your requirement but this may help:CREATE MULTISET TABLE MY_DB.PRUEBA01(THE_Name VARCHAR(5) , THE_Number SMALLINT, THE_Status CHAR(1), THE_Date DATE) PRIMARY INDEX (THE_Name, THE_Number, THE_Status, THE_Date); *** Table has been created. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:INSERT INTO MY_DB.PRUEBA01 VALUES ( 'Dave', 234, 'A', '2009-02-02'); *** Insert completed. One row added. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:INSERT INTO MY_DB.PRUEBA01 VALUES ( 'Dave', 234, 'A', '2009-02-03'); *** Insert completed. One row added. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:INSERT INTO MY_DB.PRUEBA01 VALUES ( 'Dave', 234, 'A', '2009-02-04'); *** Insert completed. One row added. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:INSERT INTO MY_DB.PRUEBA01 VALUES ( 'Dave', 234, 'A', '2009-02-04'); *** Insert completed. One row added. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:INSERT INTO MY_DB.PRUEBA01 VALUES ( 'Dave', 234, 'B', '2009-02-05'); *** Insert completed. One row added. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:INSERT INTO MY_DB.PRUEBA01 VALUES ( 'Dave', 234, 'A', '2009-02-06'); *** Insert completed. One row added. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:INSERT INTO MY_DB.PRUEBA01 VALUES ( 'Dave', 234, 'A', '2009-02-07'); *** Insert completed. One row added. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:INSERT INTO MY_DB.PRUEBA01 VALUES ( 'Dave', 234, 'A', '2009-02-07'); *** Insert completed. One row added. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:SELECT THE_Name , THE_Number, THE_Status, THE_Date (FORMAT 'DD/MM/YYYY') , SUM(CASE WHEN THE_STATUS = THE_STATUS_PREV THEN 0 ELSE 1 END ) OVER (ORDER BY THE_Date ROWS UNBOUNDED PRECEDING) as PSEUDO_RANK FROM ( SELECT THE_Name , THE_Number, THE_Status, THE_Date , MAX(THE_STATUS) OVER (ORDER BY THE_DATE ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) THE_STATUS_PREV FROM MY_DB.PRUEBA01 ) a; *** Query completed. 8 rows found. 5 columns returned. *** Total elapsed time was 1 second.THE_Name THE_Number THE_Status THE_Date PSEUDO_RANK-------- ---------- ---------- ---------- -----------Dave 234 A 02/02/2009 1Dave 234 A 03/02/2009 1Dave 234 A 04/02/2009 1Dave 234 A 04/02/2009 1Dave 234 B 05/02/2009 2Dave 234 A 06/02/2009 3Dave 234 A 07/02/2009 3Dave 234 A 07/02/2009 3

You must sign in to leave a comment.