sql如何先排序再去重

场景

有一张部分(chat)表，记录了用户群组每次的聊天，同一个人可能在多个房间有多个聊天。
（id:：id ，u_id：用户id，content：聊天内容，room_id:聊天房间，is_status:0未读 1已读）

id	u_id	content	room_id	is_status
1	2	您好	16303799156113	0
2	1	您好	16303799156113	1
3	2	您好呀	16303799156113	0
4	1	您好	16307467073008	0
5	2	您好	16307467073008	0
6	3	您好	16307467073008	0
7	1	您好	16307467073008	0

需求：找出每个聊天房间里最新没有读的消息。

SQL.1

首先我们写个最简单的sql：

select 
	id,u_id,content,room_id,is_status
from 
	chat
order by 
	room_id desc

如果sql这样写，结果可能是：

id	u_id	content	room_id	is_status
1	2	您好	16303799156113	0
2	1	您好	16303799156113	1
3	2	您好呀	16303799156113	0
4	1	您好	16307467073008	0
5	2	您好	16307467073008	0
6	3	您好	16307467073008	0
7	1	您好	16307467073008	0

排序了，但是没有去重

SQL.2

那么我们加上去重：

select 
	distinct u_id 
from 
	chat
order by 
	room_id desc

首先第一点是这个sql未必能执行。
在一些数据库版本，这个sql可以被执行，在一些版本则会提示你order by的字段必须在distinct中存在
但是即使能执行，这个sql也得不到预期结果。原因是distinct优先于order by 被数据库执行。
比如真的执行这个sql，可能去重的结果是：

id	u_id	content	room_id	is_status
1	2	您好	16303799156113	0
4	1	您好	16307467073008	0

在执行distinct room_id的时候，如上文中的数据。是取id=1的数据，还是id=3的数据呢？其实这是数据库自行决定的。因此，可能会不正确选择数据。

那么我们把room_id加入select中呢？

select 
	distinct u_id , room_id
from 
	chat
order by 
	room_id desc

很明显，这样写的执行结果和我们预期不符。

但是问题是正因为把room_id当做去重的条件了。所以对于相同的人，比如都是16303799156113，会因为其有两个数据，导致不能被去重，从而保留两行记录。结果就是好像没有去重

SQL.3

那我不用distinct，用group by进行去重可以吗？

select 
	u_id
from 
	chat
group by
	room_id
order by 
	room_iddesc

也不行，因为在group by的时候，数据库还是不知道对两行u_id一样的数据，究竟应该留下哪一行。

正确的写法：

select 
	u_id
from 
	chat
group by
	room_id
order by 
	max(id) desc

这样写，在执行group by的时候，数据库就知道要保留id最大的那一行了。