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