Reputation: 1085
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:
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.
Upvotes: 3
Views: 1006
Reputation: 1271241
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.
Upvotes: 3