select statement for message feed

I have table "Messages" in MySQL database. It has three columns: msg_id, user_id, msg_content

Users add/remove messages freely.

I want to display a feed that:

1. display messages ordered by id.

2. display only 2 messages by the same user, if the table happens to have more than 2 messages in a row by the same user.

3. display 10 messages at a time

For example, if we have:

msg_id 1, user_id A

msg_id 2, user_id A

msg_id 3, user_id A

msg_id 4, user_id B

msg_id 5, user_id C

msg_id 6, user_id B

msg_id 7, user_id B

msg_id 8, user_id B

msg_id 9, user_id A

msg_id 10, user_id F

msg_id 11, user_id D

msg_id 12, user_id E

...

The feed will be something like this:

msg_id 1, user_id A

msg_id 2, user_id A

msg_id 4, user_id B

msg_id 5, user_id C

msg_id 6, user_id B

msg_id 7, user_id B

msg_id 9, user_id A

msg_id 10, user_id F

msg_id 11, user_id D

msg_id 12, user_id E

what is the best way implement the feed "Select" stat开发者_JS百科ement in mysql?

Thank you

If every user posted 100 messages in a row you would have to fetch 500 rows... While it is possible to do it in stored procedure it is much easier with additional column, lets say "num", counting messages in a row from the same user. While inserting a message from user id check id and num of last user that posted a message with:

SELECT user_id, num FROM messages ORDER BY msg_id DESC LIMIT 1

and insert num+1 if user_id of new message matches the one you got from query or insert with num=1 if not. Then you can get your feed with simple query:

SELECT * FROM messages WHERE num<=2 LIMIT 10