Is there any way to sort values of this table in mysql?-CodePudding

I have two table one is for chats and other for users and i have some user_id. i need to sort the user id using last message send by two id.

User table:

id	name	email	password
6	xyz	xyz@g	123
5	abc	abc@g	123
7	pqr	pqr@g	123
4	lmn	lmn@g	123

Chat table:

id	sender	receiver	message	timeline
102	6	5	123	2021-10-16 03:09:08
103	6	7	123	2021-11-17 05:20:28
110	6	5	123	2021-12-16 06:19:20
112	6	4	123	2021-10-18 11:29:08

I need to sort the user table using the highest id in chat table. for example here 112 is highest chat id and its receiver id is 4 the i need to sort my user table like

4 and then 5 which has chat_id 110 and then 7 which has 103

so user table need to look like:

id	name	email	password
4	lmn	lmn@g	123
5	abc	abc@g	123
7	pqr	pqr@g	123

6 is not there so no need of adding in the resulted table

i did'nt mean to update the table. i mean to select those values from table

CodePudding user response：

Use GROUP By and MAX aggregation to find applicable chat id for each user.

SELECT a.*
FROM users a 
JOIN (
    SELECT receiver, MAX(id) AS id
    FROM chat 
    GROUP BY receiver
) c ON a.id = c.receiver
ORDER BY c.id DESC

SELECT a.id, MAX(a.name) AS name, MAX(a.email) AS email, MAX(a.password) AS password
FROM users a 
JOIN chat c ON a.id = c.receiver
GROUP BY a.id
ORDER BY MAX(c.id) DESC