Ignoring duplicates based on a criteria except one

Question

I have a table in ms access that gets updated daily. I need to remove all the duplicates posted within the same start date based on Policy number except to keep only 1 record with the highest number in the survey_number column.

For Example, in the screenshot below, we have two records for policy number "4204556734" on the same start date, I want to remove the row with Poor ranking and keep the better of the choices i.e. Excellent. I created a Survey_number column to use the MAX() function but that doesn't work either. I know I should provide a query I tried, but nothing really seems to be working so I am skipping it.

Answer 1

You can use a correlated subquery:

select t.*
from t
where t.survey_number = (select max(t2.survey_number)
                         from t as t2
                         where t2.policynumber = t.policynumber and
                               t2.startdate = t.startdate
                        );