Home » SQL & PL/SQL » SQL & PL/SQL » using analytic function
using analytic function [message #190613] Thu, 31 August 2006 07:02 Go to next message
eokumus
Messages: 1
Registered: August 2006
Junior Member
i have a table with two cols A and B.

A B
1 10000
1 10001
1 10002
1 10006
1 10007
2 10008
2 10009
2 10010
2 10011
2 10012
2 10015
3 10021
3 10022
3 10023
3 10031
3 10032
3 10033
3 10034
3 10035
4 10041
4 10042
4 10044

i want to get the following output

A B C
1 10000 10002
1 10006 10007
2 10008 10015
3 10021 10023
3 10031 10035
4 10041 10042
4 10044 10044

could anyone help me ?

[Updated on: Thu, 31 August 2006 07:04]

Report message to a moderator

Re: using analytic function [message #190625 is a reply to message #190613] Thu, 31 August 2006 07:52 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
How did you come around this result??
could you elaborate your requirement???
Re: using analytic function [message #190633 is a reply to message #190625] Thu, 31 August 2006 08:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I am assuming that you are looking for the start and end points of sets of rows with contiguous values of col b.
If this is the case, your results are slightly out.

Here is a query that will do this:

create table contig (col_1  number, col_2  number);

insert into contig values (1, 10000);
insert into contig values (1, 10001);
insert into contig values (1, 10002);
insert into contig values (1, 10006);
insert into contig values (1, 10007);
insert into contig values (2, 10008);
insert into contig values (2, 10009);
insert into contig values (2, 10010);
insert into contig values (2, 10011 );
insert into contig values (2, 10012);
insert into contig values (2, 10015);
insert into contig values (3, 10021);
insert into contig values (3, 10022);
insert into contig values (3, 10023);
insert into contig values (3, 10031);
insert into contig values (3, 10032);
insert into contig values (3, 10033);
insert into contig values (3, 10034);
insert into contig values (3, 10035);
insert into contig values (4, 10041);
insert into contig values (4, 10042);
insert into contig values (4, 10044);

select distinct 
       col_1
      ,decode(prev_val,null,col_2,lag(col_2)  over (partition by col_1 order by col_2)) first_val       
      ,decode(next_val,null,col_2,lead(col_2) over (partition by col_1 order by col_2)) last_val
from(select col_1
           ,col_2
           ,case when lead(col_2) over (partition by col_1 order by col_2) - col_2 > 1 THEN null
                 else lead(col_2) over (partition by col_1 order by col_2)   
                 end  next_val
           ,case when col_2 - lag(col_2)  over (partition by col_1 order by col_2)  > 1 THEN null
                 else lag(col_2) over (partition by col_1 order by col_2)   
                 end  prev_val
     from contig)
where next_Val is null or prev_val is null;
Re: using analytic function [message #190913 is a reply to message #190633] Sat, 02 September 2006 07:27 Go to previous messageGo to next message
zozogirl
Messages: 77
Registered: November 2005
Location: Seoul, Korea
Member

Make it simple!

SELECT   col_1 a
       , MIN (col_2) b
       , MAX (col_2) c
    FROM (SELECT col_1
               , col_2
               , col_2 - ROW_NUMBER () OVER (PARTITION BY col_1 ORDER BY col_2) grp
            FROM contig)
GROUP BY col_1
       , grp

Query Your Dream & Future at SoQooL
http://www.soqool.com
Re: using analytic function [message #190936 is a reply to message #190913] Sat, 02 September 2006 14:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
O/T:

Zozogirl, I am SO curious for this website in your sig. Is it your own? If so, I really think you should try to translate it in English, especially if it has the same high standard as your posts here.
I think a lot of people here would agree.
Re: using analytic function [message #192662 is a reply to message #190913] Wed, 13 September 2006 03:32 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Very nice indeed.
That's my learning experience for the day taken care of.
Previous Topic: sql basic questions
Next Topic: how to select from a ROWTYPE variable
Goto Forum:
  


Current Time: Wed Dec 07 18:35:27 CST 2016

Total time taken to generate the page: 0.29373 seconds