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

用开窗函数就可以呀：

Answer 2

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 3

select userid,
     min(status) over(partition by userid) status, 
     min(order) over(partition by userid) order
from logs

Answer 4

使用group by后，select只能选择group by中的字段和其他聚合运算字段，不能再用select * 的写法，mysql的支持只能说是例外。

你要的结果是按照userid分组，每个分组中保留order字段最大的行，具体实现可以参考@
slim_浩然的答案。

如果不想用oracle的分析函数，也可用下面替代的写法(要求同一个userid中不能有order重复的记录）：

select t1.*
from logs t1 inner join (select userid, max(order) as max_order from logs group by userid) t2 
  on t1.userid = t2.userid and t1.order = t2.max_order