Get top 3 entries for each game_index

Question

I have a (simplified) table named source:

game_index  int,
rating      int,
attributes  varchar(42)

And now I'm looking for a select command, that extracts the top 3 records (rating) for each game (game_index). I want to store the results into another table (called max, same table layout). So multiple SQL commands are possible.

Without game_index it is easy:

INSERT INTO max
SELECT * FROM source
ORDER BY rating DESC LIMIT 3

How to combine it with GROUP BY game_index? Any ideas?

Additional table members or temporary tables are possible.

What if there are ties? – Gordon Linoff Apr 05 '19 at 19:44 — Gordon Linoff, Apr 05 '19 at 19:44
mysql versoin please – Zaynul Abadin Tuhin Apr 05 '19 at 19:45 — Zaynul Abadin Tuhin, Apr 05 '19 at 19:45

score 1 · Accepted Answer · answered Apr 05 '19 at 19:45

In MySQL 8+, you would do:

 INSERT INTO max (. . . )  -- list the columns here
    SELECT . . .  -- list the columns here
    FROM (SELECT s.*,
                 ROW_NUMBER() OVER (PARTITION BY game_index ORDER BY rating DESC) as seqnum
          FROM source s
         ) s
    WHERE seqnum <= 3;

This is harder in earlier versions. One method that works if rating is unique is:

INSERT INTO max ( . . . )  -- list columns here
    SELECT . . . 
    FROM source s
    WHERE s.rating >= ANY (SELECT s2.rating
                           FROM source s2
                           WHERE s2.game_index = s.game_index
                           ORDER BY s2.rating DESC
                           LIMIT 1 OFFSET 2
                          );

Get top 3 entries for each game_index

1 Answers1