CODEWITHSUNDEEP
sqlmysqlgreatest-n-per-group
random55645
random55645

Reputation: 69

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.

Upvotes: 0

Views: 92

Answers (2)

ORA-01017
ORA-01017

Reputation: 1075

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)

Upvotes: 0

Anuj Karki
Anuj Karki

Reputation: 601

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.

Upvotes: 1

Related Questions