6 Replies Latest reply: Nov 22, 2012 12:20 PM by user10857924

# Analytic functions --- How to partition with respect to the order?

Imagine I have a table like this:
`````` 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.

How od I do this?
• ###### 1. Fixed Difference
Hi,

Here's one way:
``````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.

For an explanation of the Fixed Difference technique, see {message:id=9953384} and/or      {message:id=9957164}
• ###### 2. Re: Analytic functions --- How to partition with respect to the order?
``````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``````
• ###### 3. Re: Analytic functions --- How to partition with respect to the order?
A different way...
``````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``````
HTH
Ranit B.
• ###### 4. Re: Analytic functions --- How to partition with respect to the order?
or may be like this...
``````select 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     ``````
• ###### 5. Re: Analytic functions --- How to partition with respect to the order?
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``
?
• ###### 6. Re: Analytic functions --- How to partition with respect to the order?
Agree :) thanks!!

Edited by: user10857924 on Nov 22, 2012 1:20 PM