Getting the last row in each group by?

Question

Hello basically im trying to copy the messaging system that facebook has onto my site.

This is the logic... "When a user1 CREATES A NEW MESSAGE to send to user7, A new thread is created with thread_id of 1(table: messages_thread) and a new entry is inserted into table:messages which is message_id 1(table:messages). When user7 REPLYS to user1's message, message2 is created, and it has a thread_id of 1.

Now when user 1 CREATES A NEW MESSAGE to sent to user7 thread 2 is created, and message 3 is created. When user7 replies to thread2, message 4 is created (hopefully you get the logic.)

Everything is fine. the only problem is i need to select the newest message in the thread but im having trouble with the sql,

This sql that I have as of right now...

SELECT max(message_id) message_id, m.thread_id, m.body, m.user_id,m.to_id, m.message_status, m.new, m.date, u.id, u.displayname, u.username, u.profile_img
FROM messages m INNER JOIN users u ON u.id = m.user_id
WHERE to_id = 7  AND (message_status = 'unread' or message_status='read' or message_status='saved') 
group by thread_id Order by message_id Desc LIMIT 10

Produces this...

+------------+-----------+----------------------+---------+-------+----------------+-----+------------+----+--------------+----------+-------------+
| message_id | thread_id | body                 | user_id | to_id | message_status | new | date       | id | displayname  | username | profile_img |
+------------+-----------+----------------------+---------+-------+----------------+-----+------------+----+--------------+----------+-------------+
|          6 |         2 | Really nice          |       1 |     7 | read           |   0 | 1298617367 |  1 | Kenny  Blake | imkenee  | 28_1        |
|          4 |         1 | Whats good with you? |       1 |     7 | read           |   0 | 1298607438 |  1 | Kenny  Blake | imkenee  | 28_1        |
+------------+-----------+----------------------+---------+-------+----------------+-----+------------+----+--------------+----------+-------------+

This is good but one small problem, it selects the first row in each group and im trying to select the newest (the last row) in each group

how can i do this? here is the tables. Thanks!

Table: Messages_thread

+----+---------+----------------+-------------+-----------+---------------+-------------+------------+
| id | user_id | subject        | from_status | to_status | from_s_delete | to_s_delete | date       |
+----+---------+----------------+-------------+-----------+---------------+-------------+------------+
|  1 |       1 | Hey Kenny      | unread      | unread    |             0 |           0 | 1298607438 |
|  2 |       7 | Check out this | unread      | unread    |             0 |           0 | 1298617344 |
+----+---------+----------------+-------------+-----------+---------------+-------------+------------+

Table Messages

+------------+-----------+---------+-------+-----------------------------------------------------------+----------------+-----------------+-----+------------+
| message_id | thread_id | user_id | to_id | body                                                      | message_status | is_sent_deleted | new | date       |
+------------+-----------+---------+-------+-----------------------------------------------------------+----------------+-----------------+-----+------------+
|          1 |         1 |       1 |     7 | Whats good with you?                                      | read           |               0 |   0 | 1298607438 |
|          2 |         1 |       7 |     1 | Nothing Kenny just chilling. Whats up with you though???? | read           |               0 |   0 | 1298607473 |
|          4 |         1 |       1 |     7 | Just posted victor how are you man?                       | read           |               0 |   0 | 1298607956 |
|          5 |         2 |       7 |     1 | Look at this poem....                                     | read           |               0 |   0 | 1298617344 |
|          6 |         2 |       1 |     7 | Really nice                                               | read           |               0 |   0 | 1298617367 |
|          7 |         2 |       7 |     1 | Yea i know right :)                                       | unread         |               0 |   0 | 1298617383 |
+------------+-----------+---------+-------+-----------------------------------------------------------+----------------+-----------------+-----+------------+

Answer 1

Group the threads in a subquery, which will return the last message for each thread:

SELECT m.message_id, m.thread_id, m.body, m.user_id,
    m.to_id, m.message_status, m.new, m.date, u.id, u.displayname, u.username, u.profile_img
FROM messages m 
  INNER JOIN users u ON u.id = m.user_id
  INNER JOIN (
    SELECT MAX(message_id) MaxMsgIDForThread
    FROM messages
    WHERE to_id = 7
      AND (message_status = 'unread'
        or message_status='read'
        or message_status='saved') 
    GROUP BY thread_id
  ) g ON m.message_id = g.MaxMsgIDForThread
Order by m.message_id Desc
LIMIT 10

The WHERE may need to be moved to the outer query, right now it will pick the last message the meets the criteria, move it to the outer query if you want to skip the thread entirely if the conditions are not met.

You should also consider storing the message status as a ENUM which will help the comparisons.

Answer 2

There is no way to add order to group by
But maybe this works:

SELECT max(message_id) message_id, MAX(m.thread_id), m.body, m.user_id,m.to_id, m.message_status, m.new, m.date, u.id, u.displayname, u.username, u.profile_img
FROM messages m INNER JOIN users u ON u.id = m.user_id
WHERE to_id = 7  AND (message_status = 'unread' or message_status='read' or message_status='saved') 
group by thread_id Order by message_id Desc LIMIT 10