0

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.

  • State your DBMS. And what should happen if more than one name share the same (max) age. – ypercubeᵀᴹ Nov 23 '14 at 1:52
  • I'm using Teradata Web SQL Assistant. If two names have the same max age, show both. – user50887 Nov 23 '14 at 1:58
1

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.

1

The WHERE clause needs a condition, if you type:

SELECT Name FROM MyTable
WHERE MAX(Age);

there is no condition. Try to translate it to a sentence:

Select the name from MyTable where maximum age...

Now compare it with this statement:

Select the name from MyTable where the age field is equals to (select the maximum age)

And now in SQL code:

SELECT Name FROM MyTable
WHERE Age = (SELECT MAX(Age) FROM MyTable);

Hope it helped.

0

There could be more than one names that share the same maximum age. To find them all, you can use the fairly standard SQL statement, which should work in almost all DBMS:

SELECT Name 
FROM MyTable
WHERE Age = (SELECT MAX(Age) FROM MyTable) ;

Only in some DBMS, like SQL-Server (and Teardata I think), you could use this as well:

SELECT TOP (1) WITH TIES Name 
FROM MyTable
ORDER BY Age DESC ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.