Help with SQL query

Table person:

 id | name
----+-------
  1 | John
  2 | Dave
  3 | Bill


Table promotion:

 id | personid | rank           |  thedate
----+----------+----------------+------------
  1 |        2 | Private        | 2003-03-01
  2 |        1 | Private        | 2003-04-02
  3 |        1 | Corporal       | 2003-05-04
  4 |        3 | Private        | 2003-06-05
  5 |        1 | Sargeant       | 2003-07-06
  6 |        3 | Lance Corporal | 2003-08-07
  7 |        2 | Corporal       | 2003-09-21
  

Result:

 name | rank
------+-----------------
 John | Sargeant
 Dave | Corporal
 Bill | Lance Corporal

select name,rank
from person  p, promotion ap
where p.id = ap.personid
and thedate in (select max(thedate)
from promotion
where personid = ap.personid )