How Do I Select Distinct on Multiple Columns and Keep The Lowest Values On Others?

[MSAccessNewb]

Hi,



I have a table where I would like to filter certain columns for duplicates (first name, last name), and then take the lowest values in other columns (year, class rank). So for example, here is my data:

Year	Class Rank	First Name	Last Name
2006	1	Sarah	Jones
2006	2	Bob	Johnson
2006	3	Glenn	Mafoof
2006	4	Jim	Shu
2006	5	Justin	Time
2007	1	Glenn	Mafoof
2007	2	Jim	Shu
2007	3	Sarah	Jones
2007	4	Bob	Johnson
2007	5	Justin	Time
2008	1	Bob	Johnson
2008	2	Justin	Time
2008	3	Sarah	Jones
2008	4	Jim	Shu
2008	5	Glenn	Mafoof
2009	1	Sarah	Jones
2009	2	Glenn	Mafoof
2009	3	Bob	Johnson
2009	4	Jim	Shu
2009	5	Justin	Time

I would like a query to it to turn into this (ordered by last name):

Year	Class Rank	First Name	Last Name
2008	1	Bob	Johnson
2006	1	Sarah	Jones
2007	1	Glenn	Mafoof
2007	2	Jim	Shu
2008	2	Justin	Time

How do I do this? Thanks in advance.

[June7]

First you want the lowest rank then the lowest year if more than one record at same rank for each name?

Sounds difficult. Criteria dependent on data in other records of same dataset is the trickiest. Usually requires subqueries.

Review http://allenbrowne.com/subquery-01.html