Refining a Duplicate Qry to Include More Records

[liamrowan]

I need some conceptual help with how to achieve a query result. The data look something like this:

Field1 Field2 Field3 Field4
77777 2144 55555 5
77777 2144 55555 4
22222 3333 66666 2
99999 4444 77777 8

The data in the first three fields in the first two rows are identical. I want the query to look for rows where the first three fields are identical and then return only the record with the highest number in Field4. This part is easy enough using a Find Duplicates query and grouping on “Max” in Field4. HOWEVER, what I really need is a file that returns the aforementioned unduplicated row with the highest number in Field4, but ALSO includes the rows that are not duplicated in fields 1-3.

More succinctly . . . I need the query to return ALL records that do not have duplicate data in fields 1-3, and only records with the highest number in Field4 if the data in fields 1-3 are equal.

Ideas?

Thanks for assistance.

Wm

 

[plog]

Not totally following you. Using the 4 example rows of data you posted, please show what data you want returned.

 

[liamrowan]

Field1 Field2 Field3 Field4
77777 2144 55555 5
22222 3333 66666 2
99999 4444 77777 8


In this hypothetical return, row two in the initial post was removed because the first three fields were duplicates to those in row one, and in Field4, "4" is less than "5."

 

[plog]

The below SQL will do that:

SELECT Field1, Field2, Field3, MAX(Field4) AS Field4Max
FROM YourTableNameHere
GROUP BY Field1, Field2, Field3;

 

[liamrowan]

Thanks. I'm not experienced enough to know how (or where!) to use the code, but I have a co-worker who can help.