Home » SQL & PL/SQL » SQL & PL/SQL » using analytic function
using analytic function Thu, 31 August 2006 07:02
 eokumus Messages: 1Registered: 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
 gold_oracl Messages: 129Registered: July 2006 Location: Westborough, MA Senior Member
How did you come around this result??
Re: using analytic function [message #190633 is a reply to message #190625] Thu, 31 August 2006 08:39
 JRowbottom Messages: 5933Registered: 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
 zozogirl Messages: 77Registered: 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
 Frank Messages: 7880Registered: 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
 JRowbottom Messages: 5933Registered: 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: Fri Jul 28 14:21:52 CDT 2017

Total time taken to generate the page: 0.05124 seconds