Remove duplicates based on a priority in another column

[AnneForumer]

There are two "Bob" in the first grid. I'd like to remove one based on the values in "Hobby" column. "Tennis" has priority over "Hockey". The table in the bottom is the desired result.



Thank you.

[Bulzie]

Why does Tennis have the priority? What about Soccer or other Hobbies, where do they fall in the priority list and is it always that order?

[AnneForumer]

Let us make priority order as Tennis/Hockey/Soccer for this example and, yes, they are always in that order. A player can only play one game and the priority is based on costs.

Thank you!

[Bulzie]

Maybe create a new field in query to prioritize Hobby:
HobbyPriority: IIF([Hobby] = "Tennis",1,IIF([Hobby] = "Hockey",2, IIF([Hobby] = "Soccer",3,1)))

Then group by Name and select Max for HobbyPriority.

You could also create a HobbyPriority table and include Hobby and Priority fields. Then link this to your example table by Hobby and do same thing in query, group by Name, and Max by Priority.

[AnneForumer]

Thank you!

[AnneForumer]

I'd like to extend this example further. There are other fields besides Name, like city and phone number which I'd like to return but only for records that were selected based on priority returned by the solution above. Any way to do that? I can't group by those fields as those fields are not part of definition of duplicates.

[AnneForumer]

I am trying the following but it is not quite working. It is asking me to provide the value of mt.Priority "parameter" and I don't intend it to be a parameter. Hopefully, this is just a syntax error. My intent is to include priority as a computed column in main query and join it to the subquery.

SELECT mt.*, IIf([HOBBY]="TENNIS",1,IIf([HOBBY]="HOCKEY",2,3)) AS Priority
FROM MyTable AS mt
INNER JOIN (SELECT MyTable.NAME, Min(IIf([HOBBY]="TENNIS",1,IIf([HOBBY]="HOCKEY",2,3))) AS Priority
                        FROM MyTable
                        GROUP BY MyTable.NAME
                        ) as t ON mt.NAME = t.NAME and mt.Priority = t.Priority
Order by MyTable.NAME
;