ORDER BY and LIMIT in GROUP BY

Question

I am trying to get a subset of the records in GROUP BY, I saw a lot of crazy solutions there, but they seem too complicated, is there a 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 current items for all users who are still ok. But I want only the ten most recent items. Adding ORDER BY to GROUP_CONCAT helps, but it still doesn't give me the last ten records.

EDIT

If I do something like this and hardcode user_id , then I can get the results I want for this single user, the problem is combining it, so I don’t need to hardcode user_id and maybe for the instance it just gets ALL users for last ten elements

 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 

Answer 1

So I came across a pleasant solution that works very well.

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

Something like this 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 uses num increment to restrict records, not to use LIMIT

Answer 2

This is a tricky issue, but what about this:

 SELECT user_id, GROUP_CONCAT(item_id ORDER BY `timestamp`) AS items FROM wb_user_book_current_item T WHERE NOT EXISTS ( SELECT 1 FROM wb_user_book_current_item T2 WHERE T2.user_id = T.user_id ORDER BY T2.`timestamp` DESC LIMIT 10,1 ) OR T.`timestamp` > ( SELECT T2.`timestamp` FROM wb_user_book_current_item T2 WHERE T2.user_id = T.user_id ORDER BY T2.`timestamp` DESC LIMIT 10,1 ) GROUP BY user_id 

This, of course, assumes that you will not have two lines with the same timestamp for the same user.

If the timestamp field is always a positive integer, you can also replace NOT EXISTS...OR with COALESCE :

 SELECT user_id, GROUP_CONCAT(item_id ORDER BY `timestamp`) AS items FROM wb_user_book_current_item T WHERE T.`timestamp` > COALESCE(( SELECT T2.`timestamp` FROM wb_user_book_current_item T2 WHERE T2.user_id = T.user_id ORDER BY T2.`timestamp` DESC LIMIT 10,1 ), 0) GROUP BY user_id 

---

The original answer, but, apparently, MySQL does not understand how to do this properly, and complains that the subtitle returns multiple rows. Of course, we want a few lines; this is a GROUP_CONCAT . Grr.

Unfortunately, I think there is no real way to use a subquery:

 SELECT T.user_id, GROUP_CONCAT((SELECT T2.item_id FROM wb_user_book_current_item T2 WHERE T2.user_id = T.user_id ORDER BY T2.`timestamp` LIMIT 10)) AS items FROM wb_user_book_current_item T GROUP BY user_id 

Otherwise, adding LIMIT anywhere limits the number of groups or limits the overall set of records by table (not group) - not what you are trying to achieve.

Answer 3

Try the following:

 SELECT user_id, GROUP_CONCAT(item_id ORDER BY `timestamp`) AS items FROM wb_user_book_current_item GROUP BY user_id LIMIT 0, 10 

Answer 4

 SELECT i.user_id, GROUP_CONCAT(i.item_id ORDER BY i.timestamp) AS items FROM ( SELECT DISTINCT user_id FROM wb_user_book_current_item ) AS du JOIN wb_user_book_current_item AS i ON i.user_id = du.user_id AND i.timestamp <= COALESCE( ( SELECT i2.item_id FROM wb_user_book_current_item AS i2 WHERE i2.user_id = du.user_id ORDER BY i2.timestamp ASC LIMIT 1 OFFSET 9 ) , '2038-01-19 03:14:07') GROUP BY i.user_id ; 

An index on (user_id, timestamp, item_id) will help increase efficiency.

Answer 5

UPDATE: I did not notice GROUP_CONCAT, so you have to use an additional query in conjunction with LIMIT

use LIMIT

 SELECT column_name(s) FROM table_name LIMIT number