CODEWITHSUNDEEP
sqlgroup-byamazon-redshift
kzs
kzs

Reputation: 141

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

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?

Upvotes: 1

Views: 284

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272006

A good old fashioned GROUP BY should do it:

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

Upvotes: 3

Mayank Porwal
Mayank Porwal

Reputation: 34046

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;

Upvotes: 1

Related Questions