This content has been marked as final.
Show 6 replies

1. Fixed Difference
Frank Kulash Nov 22, 2012 11:09 AM (in response to 925144)Hi,
Here's one way:
If you'd care to post CREATE TABLE and INSERT statements for your sample data, then I could test this.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 ;
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?
bencol Nov 22, 2012 11:05 AM (in response to 925144)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?
ranit B Nov 22, 2012 12:35 PM (in response to 925144)A different way...
giveswith 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;
HTH2 A 6 3 B 7 4 C 3 5 B 2
Ranit B. 
4. Re: Analytic functions  How to partition with respect to the order?
user10857924 Nov 22, 2012 5:53 PM (in response to 925144)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?
bencol Nov 22, 2012 6:11 PM (in response to user10857924)user10857924 wrote:
Isn't that a complicated way of doing this: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
?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?
user10857924 Nov 22, 2012 6:20 PM (in response to bencol)Agree :) thanks!!
Edited by: user10857924 on Nov 22, 2012 1:20 PM