SQL Server查询分组结果中第一条记录的方法

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

select * from
(
select mp.MsgID,m.Content,m.CreatorID,m.CreateTime,ROW_NUMBER() over(partition by m.CreatorID order by m.CreateTime desc) as new_index
from U_Account_WX_MsgProperty mp
join U_Account_WX_Messages m on mp.MsgID=m.MsgID
where mp.UserID=453 and mp.IsRead=0 and mp.IsDelete=0
and m.CreateTime between '2016-01-01' and '2016-03-30'
) result
where result.new_index=1

注意partition后面跟着的是分组字段，也就是根据CreatorID分组
ROW_NUMBER() over(partition by m.CreatorID order by m.CreateTime desc)这行语句的意思是根据CreatorID分组并且按照CreateTime倒序排序，给查询好的每个组中的数据都编上行号