If two fields are equal select only the highest value

[Craig]

Hi

I would like to build a select query that pulls all records and all fields
(about 8 fields) from a table where the records are unique in terms of id
code number field.

Most records are unique in terms of their id code number field. However,
occasionally a few pairs (or even a set of 3) of the records in the table
have the same id code number value. If there is a set of matching id code
number values, then I only want to select out of that set the record that has
the highest revision number. (revision number and id code number are two
separate fields of the eight total fields in the table...i want to select all
fields)

thank you for any insight! Much appreciated

Craig

 

[Allen Browne]

Use a subquery in the WHERE clause to select only the highest revision
number.

Example:
SELECT Table1.* FROM Table1
WHERE Table1.Revision =
(SELECT Max(Revision) FROM Table1 AS Dupe
WHERE Dupe.ID = Table1.ID)
ORDER BY Table1.ID;

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[Craig]

Thank you very much Allen...I think this will be very helpful to me!

Craig