Oracle 常用 group by 来进行分组查询,但这里使用row_number() over()函数,语法: row_number()over(partition by 字段1 order by 字段2 ),字段1表示根据此字段分组,字段2表示排序,此函数计算的值就表示每组内部排序后的顺序编号。
With t1 as (
select 1 id,'AA' name_ , to_date('2019-08-17 22:14:20','yyyy-MM-dd HH24:mi:ss') createDate from dual
UNION select 2 id,'BB' name_ , to_date('2019-08-16 22:14:20','yyyy-MM-dd HH24:mi:ss') createDate from dual
UNION select 3 id,'CC' name_ , to_date('2019-08-10 22:14:20','yyyy-MM-dd HH24:mi:ss') createDate from dual
UNION select 4 id,'DD' name_ , to_date('2019-08-10 22:14:20','yyyy-MM-dd HH24:mi:ss') createDate from dual
UNION select 5 id,'AA' name_ , to_date('2019-08-08 22:14:20','yyyy-MM-dd HH24:mi:ss') createDate from dual
UNION select 6 id,'BB' name_ , to_date('2019-08-15 22:14:20','yyyy-MM-dd HH24:mi:ss') createDate from dual
UNION select 7 id,'CC' name_ , to_date('2019-08-16 22:14:20','yyyy-MM-dd HH24:mi:ss') createDate from dual
UNION select 8 id,'DD' name_ , to_date('2019-08-08 22:14:20','yyyy-MM-dd HH24:mi:ss') createDate from dual
)
select * from
(
select t1.name_,t1.createdate,row_number() over(partition by name_ order by createDate desc ) row_number from t1
)tt where tt.row_number=1 --获取分组后第一条信息