How to get records ordered by joined table field

Select last Created_at for every chat:

  SELECT m.chat_id,
         max(m.created_at) as LastActivity
    FROM chat_message as m
group by m.chat_id;

Let's find some infos about chats, by joining the chat table:

SELECT c.name,
       info.LastActivity
  FROM (  SELECT m.chat_id,
             max(m.created_at) as LastActivity
        FROM chat_message as m
    group by m.chat_id) info -- virtual table "info" from first query
  JOIN chat as c ON c.id = info.chat_id; -- add table chat to query

Now we want to add some info about the LastActivity

SELECT c.name,
       info.LastActivity,
       m2.user_id, -- Print Infos of LastActivityMessage
       m2.message
  FROM (  SELECT m.chat_id,
             max(m.created_at) as LastActivity
        FROM chat_message as m
    group by m.chat_id) info
  JOIN chat as c ON c.id = info.chat_id
  JOIN chat_message as m2 ON info.LastActivity = m2.created_at; -- Search for Message at LastActivity

In out last query we see a problem: We do not want to search for a messag with it's date! We're missing a propper primary-Key in table chat_message.

I'd suggest to calculate a unique id:

create table chat_message
(
    id int not null auto_increment primary key, -- this is the message-id which will automatically set
    chat_id int not null, -- where did the user post?
    user_id int not null, -- which user?
    message text,
    created_at datetime
);

If we insert our Messages now like this:

INSERT INTO chat_message (chat_id, user_id, message, created_at) 
  VALUES
  (2, 45, "Hello?", "2020-10-01 16:00:00"),
  (2 , 22 , "Anybody there?"  , "2020-10-01 17:00:00"),
  (1, 20, "Hello everybody", "2020-10-01 18:00:00"),
  (1, 23,  "this is a me...", "2020-10-01 18:00:05"),
  (1, 15, "another message", "2020-10-01 18:00:15");

..Ids are generated automatically:

id  chat_id user_id message         created_at
1   2       45      Hello?          2020-10-01T16:00:00Z
2   2       22      Anybody there?  2020-10-01T17:00:00Z
3   1       20      Hello everybody 2020-10-01T18:00:00Z
4   1       23      this is a me... 2020-10-01T18:00:05Z
5   1       15      another message 2020-10-01T18:00:15Z

This leads to a better query without searching for a single message with it's date:

-- Now we want to add some info about the LastActivity
SELECT c.name,
       m2.id,
       m2.created_at,
       m2.user_id,
       m2.message
  FROM (  SELECT m.chat_id,
             max(m.id) as LastActivity -- select last Id
        FROM chat_message as m
    group by m.chat_id) info
  JOIN chat as c ON c.id = info.chat_id
  JOIN chat_message as m2 ON info.LastActivity = m2.id; -- search for message with correct id

Try this:

SELECT DISTINCT `chat`.`name`
FROM `chat` JOIN `chat_message` ON `chat`.`id` = `chat_message`.`chat_id`
ORDER BY `chat_message`.`created_at` DESC

Explanation:

You only want the chat name to be returned. And you also want each value only once (that's the DISTINCT).

You ORDER BY the created_at field, and then the only thing left to do is to JOIN the tables.

Edit: you can try / improve here.