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 Twhere T.rownum = 1T表 SELECT ROW_NUMBER() OVER(PARTITION BY CODE ORDER BY UPDATE_DATETIME DESC,INSERT_DATETIME) AS rownum,* from XX
注意 INSERT_DATETIME升序ASC不用写，默认就是按升序排的
查询结果
| rownum | FILING_NO | AGENT_CODE | INSERT_DATETIME | UPDATE_DATETIME |
| 1 | E0001 | A0001 | 2017-05-09 17:37:17.527 | 2017-05-09 17:37:17.527 |
| 2 | E0002 | A0001 | 2017-05-10 17:37:17.527 | 2017-05-09 17:37:17.527 |
| 1 | E0002 | A0002 | 2017-05-10 17:37:17.527 | 2017-05-10 17:37:17.527 |
| 2 | E0001 | A0002 | 2017-05-09 17:37:17.527 | 2017-05-09 17:37:17.527 |