how to get the youngest policeman

[jasongr]

Hello

Given the following table definition, I would like to obtain the ID of the younger policeman and his birth date:

CREATE TABLE `people` (
  `Person_ID` int unsigned not null,
  `Job` varchar not null,
  `Birthdate` datetime not null,
  PRIMARY KEY  (`Person_ID`)
) ENGINE=MyISAM default charSET=latin1;

here was my unsuccessful attempt:

SELECT Person_ID, Birthdate
FROM people
WHERE Birthdate >= (
    SELECT Birthdate
    FROM people
    WHERE Job = 'Policeman');

Any help would be appreciated

[jreider]

my untested answer would be:

SELECT Person_ID, MIN(Birthdate)
FROM people
WHERE Job = 'Policeman'
GROUP BY Person_ID;

[Fire59]

SELECT Pserson_ID, Birthdate FROM people WHERE Job='Policeman' ORDER BY Birthdate ASC LIMIT 1

This should select the youngest policeman by birthdate.

[jasongr]

thanks for the reponses
I will use your suggestions