Question 206120 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Order by Last Message

I have a chat app on my website. The chat messages stored in a table with timestamp.
Also I list out all users (for now it’s ordered by Username).

SELECT username
FROM accounts 
WHERE NOT id = 57 AND activation_code = 'activated'
ORDER BY username ASC;

I want to order the users by the last sent messages timestamp.
I managed to create the query to show only the last sent message’s date for the logged in user (for now his id is 57).

SELECT msg_date FROM messages
WHERE outgoing_msg_id = 57
ORDER BY msg_date DESC
LIMIT 1;

How can i combine the two?

Tags:

2

Answers


  1. 0

    Test this:

    SELECT accounts.username
FROM accounts 
JOIN messages ON messages.incoming_msg_id = accounts.id
WHERE accounts.activation_code = 'activated'
  AND messages.outgoing_msg_id = 57
GROUP BY accounts.username
ORDER BY MAX(messages.msg_date) DESC;

    I hope that there are no messages to self..

    If you need in ALL users (even there is no messages from them) then try

    SELECT accounts.username
FROM accounts 
LEFT JOIN messages ON messages.incoming_msg_id = accounts.id
                  AND messages.outgoing_msg_id = 57
WHERE accounts.activation_code = 'activated'
GROUP BY accounts.username
ORDER BY MAX(messages.msg_date) DESC;
    Login or Signup to reply.
  2. 0

    One solution is a lateral join:

    SELECT a.username
FROM accounts a
LEFT JOIN LATERAL
(
  SELECT MAX(msg.msg_date) AS max_msg_date
  FROM messages msg
  WHERE msg.outgoing_msg_id = a.id
) m ON true
WHERE NOT a.id = 57
AND a.activation_code = 'activated'
ORDER BY m.max_msg_date DESC, a.username;

    Another is using a common join with an aggregation per user:

    SELECT a.username
FROM accounts a
LEFT JOIN
(
  SELECT outgoing_msg_id, MAX(msg_date) AS max_msg_date
  FROM messages
  GROUP BY outgoing_msg_id
) m ON m.outgoing_msg_id = a.id
WHERE NOT a.id = 57
AND a.activation_code = 'activated'
ORDER BY m.max_msg_date DESC, a.username;

    Yet another way is a subquery in the ORDER BY clause:

    SELECT a.username
FROM accounts a
WHERE NOT a.id = 57
AND a.activation_code = 'activated'
ORDER BY
(
  SELECT MAX(msg.msg_date)
  FROM messages msg
  WHERE msg.outgoing_msg_id = a.id
), a.username;

    If you want to show message data along with the user account data, the lateral join is the best approach:

    SELECT a.*, m.*
FROM accounts a
LEFT JOIN LATERAL
(
  SELECT *
  FROM messages msg
  WHERE msg.outgoing_msg_id = a.id
  ORDER BY msg.msg_date DESC
  LIMIT 1
) m ON true
WHERE NOT a.id = 57
AND a.activation_code = 'activated'
ORDER BY m.max_msg_date DESC, a.username;

    Or use a window function to pick the latest message per user:

    SELECT a.username
FROM accounts a
LEFT JOIN
(
  SELECT
    msg.*,
    MAX(msg_date) OVER (PARTITION BY AS outgoing_msg_id) AS max_msg_date
  FROM messages msg
  GROUP BY outgoing_msg_id
) m ON m.outgoing_msg_id = a.id AND m.msg_date = max_msg_date
WHERE NOT a.id = 57
AND a.activation_code = 'activated'
ORDER BY m.max_msg_date DESC, a.username;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search