Ranking data to get same value

Database
Teradata Employee

Ranking data to get same value

I have following information which i want to rank

Name Number Status Date
Dave 234 A 2/2/2009
Dave 234 A 3/2/2009
Dave 234 A 4/2/2009
Dave 234 A 4/2/2009
Dave 234 B 5/2/2009
Dave 234 A 6/2/2009
Dave 234 A 7/2/2009
Dave 234 A 7/2/2009

I WANT RANK TO RESULT LIKE THIS

Name Number Status Date RANK
Dave 234 A 2/2/2009 1
Dave 234 A 3/2/2009 1
Dave 234 A 4/2/2009 1
Dave 234 A 4/2/2009 1
Dave 234 B 5/2/2009 2
Dave 234 A 6/2/2009 3
Dave 234 A 7/2/2009 3
Dave 234 A 7/2/2009 3

I 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?

1 REPLY
Junior Supporter

Re: Ranking data to get same value



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 1
Dave 234 A 03/02/2009 1
Dave 234 A 04/02/2009 1
Dave 234 A 04/02/2009 1
Dave 234 B 05/02/2009 2
Dave 234 A 06/02/2009 3
Dave 234 A 07/02/2009 3
Dave 234 A 07/02/2009 3