CODEWITHSUNDEEP
sqlteradata
Adam
Adam

Reputation: 2455

Getting the highest value of a Status

Teradata query looks like this:

Select
a,
b,
MIN(Record_Start_Date) as MIN_Record_Start_Date,
MAX(Record_End_Date) as MAX_Record_End_Date
FROM tbl
GROUP BY 1,2

Now I would like to add a third attribute "status". Problem is - I want only the "last" status - the value of a row with the highest value of "Record_End_Date".

Can anyone help, please?

Upvotes: 1

Views: 60

Answers (1)

dnoeth
dnoeth

Reputation: 60462

Switch to an OLAP function instead of an aggregate:

SELECT
   a,
   b,
MIN(Record_Start_Date) OVER (PARTITION BY a, b) AS  MIN_Record_Start_Date,
Record_End_Date AS MAX_Record_End_Date,
Status
FROM tbl
QUALIFY 
   ROW_NUMBER() 
   OVER (PARTITION BY a,b 
         ORDER BY Record_End_date DESC) = 1

Upvotes: 4

Related Questions