with t as (
select '002' as id,'aaa' as password,'2016/06/04' as createdate from dual
union all
select '002','bbb','2016/06/03' from dual
union all
select '002','bbb','2016/06/02' from dual
union all
select '002','ccc','2016/06/01' from dual
)
select id,password,max(createdate)
from t
where password='aaa'
group by id,password
union all
select id,password,max(createdate)
from t
where password<>'aaa'
group by id,password
order by 1,3 desc,2
-- 借楼上数据
with t as (
select '002' as id,'aaa' as password,'2016/06/04' as createdate from dual
union all
select '002','bbb','2016/06/03' from dual
union all
select '002','bbb','2016/06/02' from dual
union all
select '002','ccc','2016/06/01' from dual
),
m as (
select id, password, createdate,
row_number() over(partition by password order by createdate desc) rn
from t
)
select * from m where rownum <= 3 and rn =1
