with t as (select 1 "TID",1 "PID", 3 "STEPNO" from dual
union all select 2 ,2 , 1 from dual
union all select 3 ,2 , 2 from dual
union all select 4 ,3 , 4 from dual
union all select 5 ,3 , 5 from dual
union all select 6 ,4 , 6 from dual)要求,在pid 相同的情况下,找出stepno最大的项对应的tid
union all select 2 ,2 , 1 from dual
union all select 3 ,2 , 2 from dual
union all select 4 ,3 , 4 from dual
union all select 5 ,3 , 5 from dual
union all select 6 ,4 , 6 from dual)要求,在pid 相同的情况下,找出stepno最大的项对应的tid
select * from task tt where (tt.pid,tt.stepno) in (select t.pid,max(stepno) from task t group by t.pid )
select 1 "TID",1 "PID", 3 "STEPNO" from dual
union all select 2 ,2 , 1 from dual
union all select 3 ,2 , 2 from dual
union all select 4 ,3 , 4 from dual
union all select 5 ,3 , 5 from dual
union all select 6 ,4 , 6 from dual)select tid,pid,stepno from(
select tid,pid,stepno,row_number() over(partition by pid order by stepno desc) rn from t
) where rn = 1