SQL SELECT with limit on ID

Question

Let's say I have a database table (posts) with fields ID, Msg and time_id. A user (ID) can post any number of messages.

I can extract the last 15 messages by:

SELECT * FROM posts ORDER BY time_id DESC LIMIT 0,15

Because a user can post unlimited messages, I need to avoid displaying too many messages from one user, so I want to LIMIT to a maximum of 3 posts from one user but still fetch 15.

How do I fetch last 15 recent messages and ensure that I get a maximum of three from any user who might have more that 3 posts in the recent 15 messages

I don't think GROUP helps here as that'd collapse results per user (messages). There's no logic you can apply to solve this problem on the database layer, do it in your application code. — marekful, Nov 19 '13 at 11:39
possible duplicate of [mysql: Using LIMIT within GROUP BY to get N results per group?](http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group) — eggyal, Nov 19 '13 at 11:41

score 0 · Answer 1 · answered Nov 19 '13 at 12:38

this might help you.You need to fire two query for that.

SELECT DISTINCT(userid) FROM `users` GROUP BY userid ORDER BY insertdate ASC LIMIt 3
 for(){
SELECT msg FROM `msgtable` WHERE userid = forloopuserid ORDER BY insertdate DESC LIMIT 0,3
    //store data in a user array 
 }

Your array might look like:

array(1=>array('msg1','msg2','msg3'),
2=>array('msg1','msg2','msg3')
);

score 0 · Answer 2 · answered Nov 19 '13 at 16:06

You can use this trick to get for each post it's position within a user

SELECT id, USER, @n:=if(USER=@last_user, @n+1, 0)  AS n , @last_user:=USER
FROM posts, (SELECT @n:=0) init
ORDER BY USER, time_id;

Then do a Join or another select from it and limit n < 3 :

SELECT posts.* FROM posts JOIN (
    SELECT id, USER, @n:=if(USER=@last_user, @n+1, 0)  AS n , @last_user:=USER
    FROM posts, (SELECT @n:=0) init
    ORDER BY USER, time_id) ranked USING (id)
WHERE n < 3
ORDER BY time_id
LIMIT 15

It works (see the sqlfidle) , however I'm not sure that the most efficient way. If you need this request often I would suggest than you run the first query in the separate (denormilazed) table and update it when needed.

Also, if you run it throug PHP, the SELECT @N:=0 usually doesn't work in a join. It needs to be done separately in a previous query.

SQL SELECT with limit on ID

2 Answers2