Extracting the smallest age. MIN with SYSDATE?

[antgaudi]

I'm trying to get the youngest pilot's name and age but all the pilots show up:

select pil_pilotname, round(min(sysdate-pil_brthdate)/365.25,0) "Age"
from pilots
group by pil_pilotname;

What's missing?
Thanks.

[SDas]

antgaudi, What your query is doing is returning the Minimum Age of every distinct Pilot name. You need to find the Minimum Age so you can use that to filter out the row you want. One way to do that is with a Sub Query.

I don't know Oracle SQL statements or syntax so I can't give you the exact statement. Here is the statement using DB2 syntax:

SELECT PIL_PILOTNAME
, YEAR(CURRENT_DATE - PIL_BRTHDATE) AS AGE
FROM PILOTS
WHERE CURRENT_DATE - PIL_BRTHDATE =
(SELECT MIN(CURRENT_DATE - PIL_BRTHDATE)
FROM PILOTS)
;

Making a guess at Oracle syntax:

SELECT PIL_PILOTNAME
, ROUND( MIN( SYSDATE - PIL_BRTHDATE ) / 365.25, 0) "Age"
FROM PILOTS
WHERE SYSDATE - PIL_BRTHDATE =
(SELECT MIN( SYSDATE - PIL_BRTHDATE)
FROM PILOTS)
;

[antgaudi]

I'll try that. Thanks.