Search code examples
sqloracle-databasetop-n

Display the name and max age

Possible Duplicate:
How to display the record with the highest value in Oracle?

I am supposed to display the name and age of the oldest person from the database. The code I used displays the name and the calculated age of several people, but it won't display the single oldest person. How do I get this to work? here is my code

select dr_drvname as "Name", trunc(max((sysdate-dr_brthdate)/365)) as "Highest AGE"
from driver,dual
group by dr_drvname;
Solution

  • Another way in Oracle would be something like:

    SELECT
    dr_drvname AS "Name",
    trunc(months_between(sysdate, dr_birthdate) / 12) AS "Highest Age"
FROM
(
SELECT
    dr_drvname,
    dr_birthdate
FROM
    driver
ORDER BY
    dr_birthdate
)
WHERE
    ROWNUM = 1;