按用户分组查找创建时间最大的对应的消息ID

[smileruner]

MsgId PeopleCode CreateTime
T_PERSON00016a9026c12960f 00646 2019-05-07 10:37:00.000
T_PERSON00016a9026c17e61b 00409 2019-05-07 10:37:00.000
T_PERSON00016a9026c1fe62b 00466 2019-05-07 10:37:00.000
T_PERSON00016a9026c270639 00411 2019-05-07 10:37:00.000
T_PERSON00016a9026c2be646 00617 2019-05-07 10:37:00.000
T_PERSON00016a9026c30d654 00423 2019-05-07 10:37:00.000
T_PERSON00016a9026bd1b574 00405 2019-05-07 10:36:59.000
T_PERSON00016a9026bd70582 00436 2019-05-07 10:36:59.000
T_PERSON00016a9026bdf3591 00365 2019-05-07 10:36:59.000
T_PERSON00016a9026be735a0 00391 2019-05-07 10:36:59.000
T_PERSON00016a9026beb85aa 00637 2019-05-07 10:36:59.000
T_PERSON00016a9026bf025ba 00370 2019-05-07 10:36:59.000
T_PERSON00016a9026bf565c7 00855 2019-05-07 10:36:59.000
T_PERSON00016a9026bfb15d6 00419 2019-05-07 10:36:59.000
T_PERSON00016a9026c01d5e5 00408 2019-05-07 10:36:59.000
T_PERSON00016a9026c0765f2 00421 2019-05-07 10:36:59.000
T_PERSON00016a9026c0c6600 00520 2019-05-07 10:36:59.000

按peoplecode分组,查询每个peoplecode creattime最新的那条记录的msgid.msgid是无序的.

[RINK_1]

SELECT *
FROM TABLE A
WHERE NOT EXISTS (SELECT 1 FROM TABLE WHERE PEOPLECODE=A.PEOPLECODE AND CREATETIME>A.CREATETIME)

[q159846]

select (select top 1 msgid from 表 as a where a.peoplecode =c.peoplecode order by creattime desc) as msgid
from (select peoplecode from 表 group by peoplecode) c