怎么用sql找最近使用的2个密码？

[charlesxu]

User表结构如下：

ID PassWord CreatedDate
002 aaa 2016/06/04
002 bbb 2016/06/03
002 bbb 2016/06/02
002 ccc 2016/06/01

如果用户002的当前密码是aaa，那么2016/06/04的密码与当前密码相同跳过，2016/06/03的密码bbb与当前密码不同，所以选中，2016/06/02的密码与当前密码不同但是与已选密码bbb相同跳过，2016/06/01的密码和当前密码与已选密码bbb都不同，所以选中。

最后该用户最近3次使用的密码是aaa，bbb，ccc

[ghx287524027]

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 

[卖水果的net]

-- 借楼上数据 
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