extracting highest value row

Database
Enthusiast

extracting highest value row

my data is as follows, how do i get teradata to return just one row, so that the max RANK of 4 and DATE 16/07/2012 is returned?

 

DATE          RANK

14/07/2010      1
16/07/2010      2
16/07/2010      3
16/07/2012      4

2 REPLIES
Senior Apprentice

Re: extracting highest value row

Simply add a QUALIFY like this:

QUALIFY RANK() OVER (ORDER BY "rank" DESC) = 1 -- or ROW_NUMBER() 
QUALIFY "rank" = MAX("rank") OVER ()

Is this "rank" an actual column or is it calculated?

 

 

Enthusiast

Re: extracting highest value row

i just used the name to illustrate, an unfortunate choice!

 

it is ranked as a calculated column, in the build of a table ("switch history") i am using this field ("switch_seq") and want to get the max number in a new query

 

so will the code change you gave?

 

thanks