N L1 L2
-------------------
1 A 5
2 A 6
3 B 7
4 C 3
5 B 2
6 B 1
I want to return N L1 L2
-------------------
2 A 6
3 B 7
4 C 3
5 B 2
That is the row with the maximum L2 among the each group of L1, but the grouping made with respect to N, that is there are 2 groups of 'B' as you see.WITH got_grp AS
(
SELECT n, l1, l2
, ROW_NUMBER () OVER ( ORDER BY n )
- ROW_NUMBER () OVER ( PARTITION BY l1
ORDER BY n
) AS grp
FROM table_x
)
SELECT MAX (n) KEEP (DENSE_RANK LAST ORDER BY l2)
AS n
, l1
, MAX (l2) AS l2
FROM got_grp
GROUP BY grp, l1
ORDER BY n
;
If you'd care to post CREATE TABLE and INSERT statements for your sample data, then I could test this.with t (n,l1,l2)
as (select 1,'A',5 from dual
union all select 2,'A',6 from dual
union all select 3,'B',7 from dual
union all select 4,'C',3 from dual
union all select 5,'B',2 from dual
union all select 6,'B',1 from dual
)
select n
,l1
,l2
from (select n
,l1
,l2
,row_number() over (partition by l1,rn order by l2 desc) rn2
,rn
from (select n
,l1
,l2
,row_number() over (order by n)
- row_number() over (partition by l1 order by n) rn
from t
)
)
where rn2 = 1
order by n
/
N L L2
___ _ __________
2 A 6
3 B 7
4 C 3
5 B 2
with xx as(
select 1 n,'A' l1,5 l2 from dual union all
select 2 n,'A' l1,6 l2 from dual union all
select 3 n,'B' l1,7 l2 from dual union all
select 4 n,'C' l1,3 l2 from dual union all
select 5 n,'B' l1,2 l2 from dual union all
select 6 n,'B' l1,1 l2 from dual
)
select n,l1,l2 from xx
minus
select n,l1,l2 from(
select n,l1,l2,count(l2) over (partition by l1) cnt,row_number() over (partition by l1 order by l2 ) rn from xx
) where rn = 1 and cnt > rn;
gives
2 A 6
3 B 7
4 C 3
5 B 2
HTHselect N, L1, L2
from
(
select x.*, rank()over(partition by grp order by L2 desc nulls last) as rnk
from
(
select x.*, sum(flg)over(order by n) as grp
from
(
select t.*,
case when L1!=max(L1) over(order by N rows between 1 preceding and 1 preceding ) OR max(L1) over(order by N rows between 1 preceding and 1 preceding ) is null Then 1 END as flg
from t
)X
)X
)x where rnk=1
user10857924 wrote:
case when L1!=max(L1) over(order by N rows between 1 preceding and 1 preceding )
OR max(L1) over(order by N rows between 1 preceding and 1 preceding ) is null Then 1 END as flg
Isn't that a complicated way of doing this:
case when L1 != lag(L1) over(order by N) Then 1 END as flg
?