row_number() over (partition by a.sql_id order by a.id desc ) r

SQL> select * from test;

 ID SQL_ID
---------- ----------------------------------------------------------------------------------------------------
  1 aaa
  2 aaa
  3 aaa
  4 aaa
  2 bbb
  1 bbb
  3 bbb

7 rows selected.

select a.*,     row_number() over (partition by a.sql_id order by a.id desc ) rn from test a;

SQL> select a.*,     row_number() over (partition by a.sql_id order by a.id desc ) rn from test a;

 ID SQL_ID RN
---------- ---------------------------------------------------------------------------------------------------- ----------
  4 aaa  1
  3 aaa  2
  2 aaa  3
  1 aaa  4
  3 bbb  1
  2 bbb  2
  1 bbb  3

7 rows selected.

SQL> select * from (select a.*,     row_number() over (partition by a.sql_id order by a.id desc ) rn from test a) where rn=1;

 ID SQL_ID RN
---------- ---------------------------------------------------------------------------------------------------- ----------
  4 aaa  1
  3 bbb  1
原文地址:https://www.cnblogs.com/hzcya1995/p/13348432.html