多个分组中取每个分组中最新的一条数据

NO E0001

insert into XX(NO,CODE,INSERT_DATETIME,UPDATE_DATETIME) values('E0001','A0001','2017-05-09 17:37:17.527','2017-05-09 17:37:17.527')

insert into XX(NO,CODE,INSERT_DATETIME,UPDATE_DATETIME) values('E0001','A0002','2017-05-09 17:37:17.527','2017-05-09 17:37:17.527')

NO E0002

insert into XX(NO,CODE,INSERT_DATETIME,UPDATE_DATETIME) values('E0002','A0001','2017-05-10 17:37:17.527','2017-05-09 17:37:17.527')

insert into XX(NO,CODE,INSERT_DATETIME,UPDATE_DATETIME) values('E0002','A0002','2017-05-10 17:37:17.527','2017-05-10 17:37:17.527') //此数据是最新的

SQL

SELECT * FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY CODE ORDER BY UPDATE_DATETIME DESC,INSERT_DATETIME) AS rownum,* from XX) as T
where T.rownum = 1

T表 SELECT ROW_NUMBER() OVER(PARTITION BY CODE ORDER BY UPDATE_DATETIME DESC,INSERT_DATETIME) AS rownum,* from XX

注意 INSERT_DATETIME升序ASC不用写，默认就是按升序排的

查询结果