Compare a column with different row and column in sample table using Oracle 11g

Question

This is what I am trying to achieve -

Consider below table

CREATE TABLE SAMPLE_TAB
(
COL1  NUMBER,
COL2  NUMBER
)

Data is as follows -

INSERT INTO sample_tab
VALUES (1 ,3);

INSERT INTO sample_tab
VALUES (3 ,4);

INSERT INTO sample_tab
VALUES (5 ,8)

INSERT INTO sample_tab
VALUES (8 ,12);

INSERT INTO sample_tab
VALUES (13 ,15);

INSERT INTO sample_tab
VALUES (16 ,20);

Actual Data

If you notice, the data has some overlapping between col2 and col1 example Row 1 Col2 ( data 3) overlaps with Row 2 Col1 (data 3 again), row 3 col2 (data 8) overlaps with row 4 col1 (data 8 again).

If we see such overlaps, we need to combine the over lap and the final output should be as follows

Expected result

Any ideas how to achieve this?

Thanks, S.

look at oracle's analytic functions.

OldProgrammer
– OldProgrammer

2013-10-25 21:01:37 +00:00
Commented Oct 25, 2013 at 21:01 — OldProgrammer
– OldProgrammer, Commented Oct 25, 2013 at 21:01

Nick Krasnov · Accepted Answer · 2013-10-25 21:39:57Z

2

Here is one of the approaches, where we divide source data into logical groups with help of lag() over() analytic function, which allows us to reference previous rows in the result set, case expression to mark a group and sum() over() analytic function to form a group. And finally we extract minimal col1 and maximal col2 in a group:

with t1 as(
  select col1
       , col2
       , sum(grp) over(order by col1) as grp
    from (select col1
               , col2
               , case 
                   when col1 <> lag(col2) over(order by col1)
                   then 1
                   else 0
                end as grp
           from sample_tab
         )
)
select min(col1) as col1
     , max(col2) as col2
  from t1
 group by grp
 order by col1

result:

      COL1       COL2
---------- ----------
         1          4 
         5         12 
        13         15 
        16         20

edited Oct 25, 2013 at 21:39

answered Oct 25, 2013 at 21:03

Nick Krasnov

27.3k6 gold badges66 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Compare a column with different row and column in sample table using Oracle 11g

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related