Geek Answers Handbook

SQL SELECT with ID constraint

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

I can extract the last 15 posts:

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

Since the user can send unlimited messages, I need to avoid displaying too many messages from one user, so I want LIMIT not to exceed 3 messages from one user, but still get 15.

How to get the last 15 last messages and make sure that I get a maximum of three from any user who could have more than 3 messages in the last 15 messages

+4
source share
2 answers

. .

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 
 }

:

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

,

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;

Join 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

(. sqlfidle), , . , , (denormilazed) .

, PHP, SELECT @N:=0 . .

0

More articles:


All Articles