Removing specific duplicates from query

[gorgon777]

Hi again,

I have a query which shows the following data and fields:

Key Grade Rate Country Code
1 2 Standard UK 3000
1 2 Standard UK 8000
1 2 Standard USA 3000
2 3 Standard UK 8000
3 1 Landed UK 3000
1 2 Landed USA 3000
1 2 Landed USA 4000

What I would like to do is show the same table again, but with the red records removed, as it is a duplicate on the first four fields, with the lowest code of the two(first duplicate: 3000 < 8000, and second duplicate: 3000 < 4000) being wanted to removed by me.

How can I filter out these specific duplicates from the query?
Thanks for any help.

 

[recyan]

Have you tried something like :

SELECT
Key,
Grade,
Rate,
Country,
MAX(Code) AS TheCode
FROM
...
GROUP BY
Key,
Grade,
Rate,
Country

Thanks

 

[gorgon777]

Thank you that was it, I cant beleive i missed that. I got so focused on trying to do a delete/append query from a tutorial that simply grouping and Max() would work too.

 

[recyan]

I cant beleive i missed that. I got so focused on trying to do

I understand.
This happens with me too, most of the times.

Thanks