How to make a correct query on related tables grouping and sorting?

Question

SQL

I have a ticket_themes room table and a ticket_message message table. I need to get the room and the last message in it.
My example does not work, I do not understand how to do it right

SELECT * FROM ticket_themes ticket LEFT JOIN ticket_message tm on ticket.id = tm.theme_id WHERE ticket.user_id = 69625 ORDER BY tm.created_at DESC

If in this case I add GROUP BY to the room id, I get an error

[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY ticket.id' at line 1

How to make the right request

Answer 1

Perhaps the problem is that the select contains an asterisk and both tables have the same columns. Try to manually specify the names of the columns you want to get.

SELECT ticket.theme_text, tm.message_text FROM ticket_themes ticket LEFT JOIN ticket_message tm on ticket.id = tm.theme_id WHERE ticket.user_id = 69625 ORDER BY tm.created_at DESC

Answer 2

SELECT *
FROM (SELECT ticket.name as ticket_name, tm.text as message_text, tm.created_at as message_dt, ROW_NUMBER() OVER(PARTITION BY ticket.user_id ORDER BY tm.created_at DESC, tm.id DESC) AS rn
  FROM 
    ticket_themes ticket 
    LEFT JOIN ticket_message tm on ticket.id = tm.theme_id 
  WHERE ticket.user_id = 69625 ) t
WHERE t.rn = 1