select id, oldid, max(type) from table group by id
SQL> select * from aa; ID OLDID TYPE ---------- ---------- ---------- 10 a1 4 10 a2 3 10 a3 1 11 a5 2 11 a6 3SQL> select a.id, 2 b.oldid, 3 a.type 4 from aa b, 5 (select id,max(type) type from aa group by id) a 6 where a.id=b.id 7 and a.type=b.type 8 / ID OLDID TYPE ---------- ---------- ---------- 11 a6 3 10 a1 4SQL>
方法二 SQL> select * From aa; ID OLDID TYPE ---------- ---------- ---------- 10 a1 4 10 a2 3 10 a3 1 11 a5 2 11 a6 3SQL> select * from ( 2 select id,oldid,type, 3 row_number() over(partition by id order by type desc nulls last) type_order 4 from aa) 5 where type_order=1 6 / ID OLDID TYPE TYPE_ORDER ---------- ---------- ---------- ---------- 10 a1 4 1 11 a6 3 1
select oldid from { select id, oldid, max, rank() over(partition by id order by type desc) rk from table } where rk=1用这个试试吧
是我想求id相同、type最大
不是只要id type
---------- ---------- ----------
10 a1 4
10 a2 3
10 a3 1
11 a5 2
11 a6 3SQL> select a.id,
2 b.oldid,
3 a.type
4 from aa b,
5 (select id,max(type) type from aa group by id) a
6 where a.id=b.id
7 and a.type=b.type
8 / ID OLDID TYPE
---------- ---------- ----------
11 a6 3
10 a1 4SQL>
SQL> select * From aa; ID OLDID TYPE
---------- ---------- ----------
10 a1 4
10 a2 3
10 a3 1
11 a5 2
11 a6 3SQL> select * from (
2 select id,oldid,type,
3 row_number() over(partition by id order by type desc nulls last) type_order
4 from aa)
5 where type_order=1
6 / ID OLDID TYPE TYPE_ORDER
---------- ---------- ---------- ----------
10 a1 4 1
11 a6 3 1
{
select id, oldid, max,
rank() over(partition by id order by type desc) rk
from table
}
where rk=1用这个试试吧