select an entire row based on Max value in a single field

S

SAM

i need to select an entire row wfrom an access database based on the Maximum
value within a single field
 
B

Brendan Reynolds

SAM said:
i need to select an entire row wfrom an access database based on the
Maximum
value within a single field
Click to expand...


SELECT * FROM core_students WHERE studentid = (SELECT MAX(studentid) FROM
core_students)

Replace "core_students" with your table name and "studentid" with your field
name.
 
S

SAM

Thanks Brendan, but not quite, it only returned a single record. I need it
to pick the highest from groups

xxxx yyyy 03
xxxx yyyy 06
xxxx yyyy 04
xxxy yyyy 03
xxxy yyyy 02

so the return would be

xxxx yyyy 06
xxxy yyyy 03
 
K

KARL DEWEY

Try this --
SELECT [X], [Y], Max([Z]) AS MaxOfZ
FROM YourTable
BROUP BY [X], [Y];
 
Top