SQL MAX function

teradata

Suppose I have a table with attributes Name, Age as follows:

MyTable (Name, Age)

Now, to get the oldest age I can write the following query:

SELECT MAX(Age) AS "Oldest Age" FROM MyTable;

However, I cannot figure out the query in the case when I only want the name of the person with the oldest age. I tried the following, but it does not work:

SELECT Name FROM MyTable
WHERE MAX(Age);

Any help appreciated.

Best Answer

I believe Teradata supports window functions, so you can use them to pick the larges value.

select name, age
from (
   select name, age, 
          dense_rank() over (order by age desc) as rn
   from MyTable
) t
where rn = 1;

If there are multiple names with the same age, all would be shown. If you only want to pick one, use row_number() instead of dense_rank()

Using window functions is very often faster than sub-selects.

Related Question