I want to sort date in mysql

Question

I have 2 tables chat_room have two columns : id, slogan chat have 3 columns : id, chat_room_id,updated_at

this is my code

SELECT chat_room.*
FROM chat_room
ORDER BY (SELECT updated_at FROM chat WHERE chat.chat_room_id = chat_room.id ) DESC;

this is my bug SELECT chat_room.* FROM chat_room ORDER BY (SELECT updated_at FROM chat WHERE chat.chat_room_id = chat_room.id) DESC LIMIT 0, 1000 Error Code: 1242. Subquery returns more than 1 row 0.00058 sec

Answer 1

Isn't it better to do a join instead of using a subquery and achieve what you want like this:

SELECT
cr.*
FROM chat_room cr
JOIN chat c on cr.id=c.chat_room_id
order by updated_at desc

Answer 2

The error is telling you that the subquery in the ORDER BY clause sometimes is returning more than one record. This raises the question of which updated_at value you want to use in the case that a given chat room might have more than one value. Assuming you want to sort by the latest updated_at value, you could use:

SELECT cr.*
FROM chat_room cr
INNER JOIN
(
    SELECT chat_room_id, MAX(updated_at) AS max_updated_at
    FROM chat
    GROUP BY chat_room_id
) c
    ON c.chat_room_id = cr.id
ORDER BY
    c.max_updated_at DESC;