sql - return one row from each group

Considering the tables below, what is the best way to only return the team membership and its 开发者_Python百科related player, but only one player from each team? The important part to note is that selection needs to be based on a given set of player ids. That is the starting point.

Using the example below I would have the player ids: 1,2,3 (among others) and what I need to end up with is the unique memberships from a list of user ids(1,2,3 in this case). I would want to end up with team_memberships with id 1 & 2

Thanks!

player
id | name
1  | bob
2  | joe
3  | tom

team_memberships
id | team_id | player_id
1  | 1       | 1
2  | 2       | 2
3  | 1       | 3

team
id | name
1  | jets
2  | kings

Do you mean

Select team_id, Min(player_id)
From team_Memberships
Where player_id In (1,2,3)
Group By team_id

If the player selected needs to be based on some other attribute in the players table, then:

Select team_id, 
   (Select Min(player_id) -- in case more than one player satisfies criterion
    From players 
    where playerId = m.Player_Id
       And [Some other ctiterion]) playerId
From team_Memberships m
Where player_id In (1,2,3)  
Group By team_id