How to write a query like this...

[Arrowx7]

Personnel Table
PID Name
1 John
2 Tom
3 Tim
4 Jay
Appointment table:
PID rank start
1 A 12/12/2003
1 B 10/12/2001
1 C 15/12/2006
4 A 13/09/2004
How do I write a query such that I get the first "rank" for John?
so a query should output
john, B
Jay, A
because rank B is the earliest date and it's associated with john

I have no idea how to do this. Have to somehow orderby and then grab the first date of that appointment table. Please point me in the right direction.

 

[PWise]

Select Pid ,Name, rank
from Personnel
inner join Appointment
on Personnel.pid = Appointment .pid
inner join (Select pid ,min(start) as firstdate
from Appointment
Group by pid )dates
on dates.pid=Appointment .pid
and dates.firstdate=Appointment .Start