Help with Most Recent Field

[maryjo2001]

I am trying to select the most recent room number based on the maximum row identifier.

Table1 contains these fields:
UID,Date,Room,PatientID

Data in Table1:
1,7/1/02,456B,Amy74
2,7/1/02,234C,Amy74
3,7/1/02,457D,Amy74

Data I would like to see based on query:
3,7/1/02,457D,Amy74

Code I wrote but did not give me what I wanted:
SELECT DISTINCT
Table1.PatientID, Table1.Date, Table1.Room, MAX(Table1.UID)
FROM Table1
GROUP BY
Table1.Date, Table1.Room, Table1.PatientID
ORDER BY
Table1.PatientID,Table1.Date ASC, Table1.UID DESC

any help would be greatly appreciated.

 

[IdoMillet]

One easy way to do this is to sort the records by UID,
move all the fields from the detail section to the lowest group's footer, and suppress the detail section.

The group footer always shows the last record in the group.

Cheers,
- Ido CUT (Crystal UTilities): e-mailing, exporting, electronic bursting & distribution of Crystal Reports:

http://sobpc02.bd.psu.edu/CUT/CUT.htm