Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Perform Same Query For Every Id in List

I have a table called scores. id is the auto-incremented primary key.

id player_id score
1 1 100
2 1 57
3 2 434
4 3 11
5 3 132
6 4 32
7 4 100
8 4 21

Given a list of player ids, I need to return the last score received from each player_id in the list (i.e. the score that has the greatest id with the given player_id).

Using the data above, player 1 should return 57, player 2 should return 434, player 3 should return 132, and player 41 should return 21.

Let’s say I am given a list containing player_ids 3 and 4. I want to create a single query that returns 132 for the first row and 21 for the second row.

The individual queries would be

select score  from scores where player_id = 3   order by id desc limit 1; 
select score  from scores where player_id = 4   order by id desc limit 1;

I’m having trouble combining these two into one query. I want to avoid using UNION because the list could contain hundreds of player_ids.

Tags:

2

Answers


  1. 0

    You can use NOT EXISTS as follows:

    select t.id, t.player_id, t.score
  from scores t
where not exists (select 1 from scores tt
                  where tt.player_id = t.player_id
                    and tt.id > t.id);

    Another option is to use ROW_NUMBER window function as follows:

    select t.id, t.player_id, t.score from
  (select t.id, t.player_id, t.score,
         row_number() over (partition by t.player_id order by t.id desc) as rn
    from scores t) t
where rn = 1;

    NOTE: You can add one more condition in WHERE clause to include only required player_id like player_id in (3,4)

    Login or Signup to reply.
  2. 0

    You could get desired result from multiple approach.

    Approach 1

    SELECT *
FROM Scores
WHERE id IN (
        SELECT Max(id)
        FROM scores
        GROUP BY player_id
        )

    In this approach, sub-query SELECT Max(id) FROM scores GROUP BY player_id gets the greatest/max id for each player_id. And gets result on the basis of those id.

    Approach 2

    SELECT s.player_id
    ,s.id
    ,s.score
FROM scores s
JOIN (
    SELECT player_id
        ,MAX(id) AS max_id
    FROM scores
    GROUP BY player_id
    ) t ON s.player_id = t.player_id
    AND s.id = t.max_id;

    In this apporoach, sub-query is quite similar to above one but it additionally returns player_id also which is used for joining to retrive the data.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search