Return one from many

[simoNamibia]

Lets say we have a table containing names and their heights. In the table
there are 10 John's and 10 Mary's all of varying heights, how would one query
this table in order to return only the one John and the one Mary whose height
is the smallest?

Thanks

 

[RonaldoOneNil]

Substitute your table name and field names but this is the SQL

SELECT Heights.PersonName, Min(Heights.Height) AS MinOfHeight
FROM Heights
GROUP BY Heights.PersonName
HAVING (((Heights.PersonName)="John" Or (Heights.PersonName)="Mary"));

Or in query design, put the person and the height in as columns. Click the
Totals button, leave person as group by but change height to Min.
Enter "John" or "Mary" as criteria