ORDER BY and LIMIT in GROUP BY

Question

I'm trying to get a subset of records in a GROUP BY, I've seen a lot of crazy solutions out there, but they just seem too complicated, is there any more efficient way to do this. ``` SELECT user_id, GROUP_CONCAT(item_id ORDER BY `timestamp`) AS items FROM wb_user_book_current_item GROUP BY user_id ``` So this will return me all the current items for all users which is okay so far. But I only want the ten most recent items. Adding `ORDER BY` to the `GROUP_CONCAT` helps, but it still doesn't give me the last ten records. EDIT If I do something like this and hard code the `user_id` then I can get the results I want for that one user, problem is combining it so that I don't need to hard code the `user_id` and can for instance just get ALL users last ten items ``` SELECT GROUP_CONCAT(cp2.item_id) AS items FROM (SELECT cp.user_id, cp.item_id FROM wb_user_book_current_item cp WHERE cp.user_id=1 ORDER BY cp.`timestamp` LIMIT 10) AS cp2 GROUP BY cp2.user_id ```

Accepted Answer

So came across a nice solution here that works pretty well.

http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

It's something like this put all together:

```
SET @num := 0, @user_id := '';

SELECT cp2.user_id, CONCAT(cp2.item_id) AS items
FROM (
   SELECT cp.user_id, cp.item_id,
   @num := IF(@user_id = cp.user_id, @num + 1, 1) AS row_number,
   @user_id := cp.user_id AS dummy
   FROM wb_user_curent_item AS cp
   ORDER BY cp.user_id ASC, cp.`timestamp` DESC
) AS cp2 WHERE cp2.row_number <= 10
GROUP BY cp2.user_id
```

So basically it just uses the `num` increment to limit the records rather than using `LIMIT`

ORDER BY and LIMIT in GROUP BY

Solution

Problem