SQL: how to pick one row for each set of rows with duplicate value in one column?

Question

I have a table which looks like this:

Player | Sport | Rank
A        Tennis  1
B        Tennis  2
C        Tennis  2
D        Tennis  2
E        Tennis  3
F        Soccer  1
G        Soccer  2

And so on.

For each partition where Sport is the same, I want to filter the results down so that each Rank for each Sport is unique. In my example above, one of B/C/D would be selected in the result, and I don't care which one is selected.

I expect the result to be (where D could also be B or C):

Player | Sport | Rank
A        Tennis  1
D        Tennis  2
E        Tennis  3
F        Soccer  1
G        Soccer  2

How can I implement this logic in SQL?

Answer 1

A good old fashioned GROUP BY should do it:

SELECT MIN(player) AS any_player, sport, rank
FROM t
GROUP BY sport, rank

Answer 2

You can use ROW_NUMBER:

Select player, sport,rank
FROM
(select player, sport,rank,
ROW_NUMBER() OVER(partition by sport,rank order by player) as rown
FROM table
)a
WHERE a.rown=1;