oracle 指定字段排序并去重？

Question

有logs表的结构为

uesrid | status    |    order

111    | 5         |    7

111    | 0         |    9

111    | 1         |    8

222    | 3         |    3

222    | 1         |    2

222    | 2         |    4

333    | 3         |    10

请问如何先以order desc排序，并通过userid去重，同时获取userid，status，order的值。
想要的结果为

uesrid | status    |    order

111    | 0         |    9

222    | 2         |    4

333    | 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

用开窗函数就可以呀：