Select several records for each user - SQL

Question

I build chat application with messages table.

I try to select 3 recent messages for each user that correspondent with specific user like that: user 17 has 3 users that correspondent with him, select last 3 messages for each user

17 22

22 17

22 17

19 17

17 19

19 17

21 17

17 21

17 21

I tried to write query, I got the good result for all users except one - the first or the last user that select for him more than 3 messages. the query:

    SELECT *, 
         DATE_FORMAT((FROM_UNIXTIME(m1.create_time)),'%k:%i, %d.%m.%Y') 
         as create_time_display
    FROM t_messages m1
    WHERE (m1.to =17 OR m1.from =17)  AND (
        SELECT COUNT(*) 
        FROM t_messages  m2
        WHERE m1.from = m2.from AND m2.id >= m1.id AND
        (m2.to = 17 OR m2.from = 17)
    ) <=3`

Any ideas how I can make it work well?

Thanks.

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) — Barmar, Sep 02 '13 at 05:01
It helps but not so, because I have two keys - the "from" user and the "to" user. — user2738629, Sep 02 '13 at 05:16
The basic idea is the same no matter how you select the records to show. — Barmar, Sep 02 '13 at 05:18
Creating a [sqlfiddle](http://sqlfiddle.com) with relevant sample data can considerably improve your chances of getting an answer you're looking for. — peterm, Sep 02 '13 at 06:35

mas.morozov · Answer 1 · 2013-09-02T08:45:23.837

SELECT m1.*, 
       DATE_FORMAT(FROM_UNIXTIME(m1.create_time),'%k:%i, %d.%m.%Y') 
       as create_time_display
FROM t_messages m1
WHERE (m1.to=17 OR m1.from=17) AND 
      (SELECT COUNT(1) 
       FROM t_messages m2
       WHERE m2.id >= m1.id AND
            (m2.to=17 and m2.from=m1.from OR m2.from=17 and m2.to=m1.to)
      ) <=3

And if t_messages.id is an autoincremented key, comparing on it is right, otherwise you should compare t_messages.create_times

Select several records for each user - SQL

1 Answers1