oracle 指定字段排序并去重？

Question

有logs表的结构为uesrid | status    |    order111    | 5         |    7111    | 0         |    9111    | 1         |    8222    | 3         |    3222    | 1         |    2222    | 2         |    4333    | 3         |    10请问如何先以order desc排序，并通过userid去重，同时获取userid，status，order的值。想要的结果为uesrid | status    |    order111    | 0         |    9222    | 2         |    4333    | 3         |    10在mysql下，可以通过以下SQL获得。SELECT * FROM (SELECT * FROM `logs` ORDER BY `order` DESC) AS a GROUP BY a.userid;但是oracle这么写语法就报错，请问oracle该如何实现？

Answer 1

WITH temp as

    (

    select '111' as userid,'5' as status,'7' as ord from dual

    union all

    select '111' as userid,'0' as status,'9' as ord from dual

    union all

    select '111' as userid,'1' as status,'8' as ord from dual

    union all

    select '222' as userid,'3' as status,'3' as ord from dual

    union all

    select '222' as userid,'1' as status,'2' as ord from dual

    union all

    select '222' as userid,'2' as status,'4' as ord from dual

    union all

    select '333' as userid,'3' as status,'10' as ord from dual

    )

select userid,status,ord from (    

select userid,status,ord,row_number() over(partition by userid order by ord desc)  rn 

from temp

)

where rn=1

;

Answer 2

用开窗函数就可以呀：