CODEWITHSUNDEEP
mysqlsql
mdnfiras
mdnfiras

Reputation: 969

sql - select rows with a distinct value in a specific column

Let's assume that I have the following table:

   id     player_name     team
   
   1      Gladiator        A
   2      DarkLord         B
   3      Alligator        A
   4      MonaLisa         C
   5      SpongBob         C
   6      Mikasa           B

I want to select one player from each team, which means that all the selected rows must have a unique value in the 'team' column. How can I accomplish this in MySQL?

In my example the selected rows should be:

   id     player_name     team
   
   1      Gladiator        A
   2      DarkLord         B
   4      MonaLisa         C

Upvotes: 2

Views: 2949

Answers (2)

Sarath Chandra
Sarath Chandra

Reputation: 1888

One simple way would be to fetch using a group by criteria. (Assuming your table name is TEAM_TABLE)

SELECT * FROM TEAM_TABLE GROUP BY TEAM;

This would return the first record occurring for each value of the team column.

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

This is one way to do it using a derived table so you select one id per team and join it to the original table.

select t.id, t.player_name, t.team
from tablename t
join (select team, min(id) as minid from tablename group by team) x
on x.team = t.team and x.minid = t.id

Upvotes: 3

Related Questions