SQL - How to select all Chats and order them by most recent Message

user_6854812 0
Viewed 203

I am creating a simple Chat application and I would like to select all Chat and order them by their most recent Message (column created_on) . The 2 tables are separated and are described as the following:

CHAT enter image description here

MESSAGE enter image description here

To achieve that, I wrote the following SQL Script:

SELECT * CHAT AS chat LEFT JOIN MESSAGE AS message ON chat.ID = message.CHAT_ID
GROUP BY chat.ID
ORDER BY message.CREATED_ON

When I run that script, the result are not ordered by most recent Message but instead randomly. And I also noticed that the Script is not fast.

sql
1 Answers

Your query is malformed, because you are using SELECT * with GROUP BY. (And this ignores the other syntax errors such as the missing FROM clause.) Your query should be returning an error.

You seem to not only want the chats to be ordered but to also include the most recent message. For that, use window functions:

SELECT c.*, m.*
FROM chat c LEFT JOIN
     (SELECT m.*,
             ROW_NUMBER() OVER (PARTITION BY m.chat_id ORDER BY m.CREATED_ON DESC) as seqnum
      FROM MESSAGE m
     ) m
     ON c.ID = m.CHAT_ID AND m.seqnum = 1
ORDER BY m.CREATED_ON DESC;

In most databases, an index on message(chat_id, created_on) would help.

user_11440350
Related