mysql - selecting limited rows of duplicate columns

Question

What I have

So I'm running this statement:

SELECT
  i.id,
  i.item_id,
  v.item_to_map_id,
  i.date,
  COALESCE( SUM(CAST(CAST(v.score AS char) AS SIGNED)), 0 ) AS score
FROM item_to_map i
LEFT JOIN
  vote_item v
    ON i.id = v.item_to_map_id
GROUP BY
  i.id, i.item_id, i.date, v.item_to_map_id
ORDER BY
  item_id asc, score desc;

And I'm getting the following table:

+----+---------+----------------+---------------------+-------+
| id | item_id | item_to_map_id | date                | score |
+----+---------+----------------+---------------------+-------+
|  1 |       1 |              1 | 2017-07-05 09:38:23 |     3 |
|  3 |       1 |              3 | 2017-07-05 09:38:23 |     0 |
|  2 |       1 |              2 | 2017-07-05 09:38:23 |    -1 |
|  4 |       2 |           NULL | 2017-07-05 09:38:23 |     0 |
|  5 |       2 |           NULL | 2017-07-05 09:38:23 |     0 |
|  6 |       2 |           NULL | 2017-07-05 09:38:24 |     0 |
+----+---------+----------------+---------------------+-------+

What I'm trying to do is select the first X of the repeated item_ids based on some ordering, for example, score or date.

What I've tried

I looked at this answer https://stackoverflow.com/a/1902167/6554121 and tried a modified version:

SELECT
  i.id,
  i.item_id,
  v.item_to_map_id,
  i.date,
  COALESCE( SUM(CAST(CAST(v.score AS char) AS SIGNED)), 0 ) AS score
FROM item_to_map i
LEFT JOIN
  vote_item v
    ON i.id = v.item_to_map_id
WHERE
     (
          SELECT
               COUNT(*)
          FROM
               item_to_map i2
          WHERE
               i2.item_id = i.item_id
     ) < 3    
GROUP BY
 i.id, i.item_id, i.date, v.item_to_map_id
ORDER BY item_id asc, score desc;

However this returns me no results

What I expected

If ordered by score:

+----+---------+----------------+---------------------+-------+
| id | item_id | item_to_map_id | date                | score |
+----+---------+----------------+---------------------+-------+
|  1 |       1 |              1 | 2017-07-05 09:38:23 |     3 |
|  3 |       1 |              3 | 2017-07-05 09:38:23 |     0 |
|  4 |       2 |           NULL | 2017-07-05 09:38:23 |     0 |
|  5 |       2 |           NULL | 2017-07-05 09:38:23 |     0 |
+----+---------+----------------+---------------------+-------+

Answer 1

You can achieve this using session variables which simulate row number functionality:

SET @row_number = 0;
SET @item_id = 1;

SELECT t.id, t.item_id, t.item_to_map_id, t.date, t.score
FROM
(
    SELECT
        @row_number:=CASE WHEN @item_id = t.item_id
                          THEN @row_number + 1 ELSE 1 END AS rn,
        @item_id:=t.item_id AS item_id,
        t.id, t.item_to_map_id, t.date, t.score
    FROM
    (
        SELECT
            i.id,
            i.item_id,
            v.item_to_map_id,
            i.date,
            COALESCE( SUM(CAST(CAST(v.score AS char) AS SIGNED)), 0 ) AS score
        FROM item_to_map i
        LEFT JOIN vote_item v
            ON i.id = v.item_to_map_id
        GROUP BY
            i.id, i.item_id, i.date, v.item_to_map_id
    ) t
    ORDER BY
        t.item_id, t.score DESC
) t
WHERE t.rn <= 2    -- this restricts to the first two rows per item_id group
                   -- as ordered by the logic in your ORDER BY clause

As far as I know, there is no nice way to get the first X records of a group in MySQL, unless your schema coincidentally happens to have row numbers already for each group. Using session variables as above is one way to handle this, and the performance might even be good as well.

Demo here:

Rextester