Getting most recent row from one-to-many relationship

I have two tables, Admission and RecordSummary. The Admission table holds
one row for each resident at our facility and the RecordSummary table holds
one row for each admission the resident has (could be more than 1). They
are linked using a Register Number, which is a 6 digit number. The
RecordSummary table has a column named AdmissionNo, which contains the
admission number.
How can I SELECT a resident and their most recent (i.e. highest AdmissionNo)
RecordSummary table row?
Thanks,
DrewDrew
select * from Admission c
join (select resident ,max(orderdate)orddate
from RecordSummary group by resident )
as der on der.resident =c.resident
order by c.resident
"Drew" <drewDOTlaingATswvtc.dmhmrsas.virginia.gov> wrote in message
news:%23EYeajbeGHA.4532@.TK2MSFTNGP02.phx.gbl...
>I have two tables, Admission and RecordSummary. The Admission table holds
>one row for each resident at our facility and the RecordSummary table holds
>one row for each admission the resident has (could be more than 1). They
>are linked using a Register Number, which is a 6 digit number. The
>RecordSummary table has a column named AdmissionNo, which contains the
>admission number.
> How can I SELECT a resident and their most recent (i.e. highest
> AdmissionNo) RecordSummary table row?
> Thanks,
> Drew
>|||Thanks!
Drew
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ecXPnpbeGHA.3900@.TK2MSFTNGP05.phx.gbl...
> Drew
> select * from Admission c
> join (select resident ,max(orderdate)orddate
> from RecordSummary group by resident )
> as der on der.resident =c.resident
> order by c.resident
>
>
> "Drew" <drewDOTlaingATswvtc.dmhmrsas.virginia.gov> wrote in message
> news:%23EYeajbeGHA.4532@.TK2MSFTNGP02.phx.gbl...
>