select col,... from (select col,..., row_number() over(partition by col order by col) rn from tb) where rn=1
这种不靠谱啊.后面插入的数据也有可能在前面的.不是早插入的数据就一定rowid较小.
能确定顺序就好. SQL> select * from t_num;
ID ---------- 92 92 92 35 92 92 35 35
8 rows selected
SQL> SQL> with tab as (select id,rownum rn from t_num) 2 select id from tab a where not exists(select 1 from tab b where a.id=b.id and a.rn=b.rn-1) 3 order by a.rn;
distinct
分析函数
多了
from (select col,...,
row_number() over(partition by col order by col) rn
from tb)
where rn=1
SQL> select * from t_num;
ID
----------
92
92
92
35
92
92
35
35
8 rows selected
SQL>
SQL> with tab as (select id,rownum rn from t_num)
2 select id from tab a where not exists(select 1 from tab b where a.id=b.id and a.rn=b.rn-1)
3 order by a.rn;
ID
----------
92
35
92
35
SQL>
逻辑很简单.就是取每个分组最后一条.最后一条的特点就是.不存在与之数值相同但是行号=它的行号+1的记录.
所以使用not exists就可以把记录筛选出来.