Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2008
    Posts
    8

    Unanswered: Simple Range-merging query?

    I have a table with the following columns:
    name, low_value, high_value

    data looks like this:
    bob, 1, 2
    bob, 4, 8
    bob, 5, 10
    dan, 3, 7
    dan, 6, 12
    dan, 15, 18

    and I want a query that can return
    bob, 1, 2
    bob, 4, 10 ---> merged from (bob, 4, 8 AND bob, 5, 10)
    dan, 3, 12 ---> merged from (dan, 3, 7 AND dan, 6, 12)
    dan, 15, 18

    basically if a low_value->high_value pair intersects, they merge

    thanks!!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Do you honestly expect us to do your homework assignment for you?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2008
    Posts
    5
    hey,you can fallow this example, i think

    SQL> select * from t2;

    ID START_DT END_DT
    --- -------- --------
    001 20080101 20080201
    001 20080115 20080301
    001 20080201 20080301
    001 20080501 30001231

    SQL> select id,min(start_dt) start_dt,max(end_dt) end_dt from(
    2 select id,start_dt,end_dt,connect_by_root(start_dt) root from(
    3 select id,start_dt,end_dt,max(parent) parent from(
    4 select a.*,b.rowid parent from t2 a left join t2 b
    5 on (a.start_dt between b.start_dt and b.end_dt
    6 or
    7 a.end_dt between b.start_dt and b.end_dt)
    8 and
    9 (a.start_dt<>b.start_dt or a.end_dt<>b.end_dt))
    10 group by id,start_dt,end_dt)
    11 start with parent is null or start_dt=date '2008-01-01'
    12 connect by nocycle start_dt between prior start_dt and prior end_dt)
    13 group by id,root
    14 /

    ID START_DT END_DT
    --- -------- --------
    001 20080101 20080301
    001 20080501 30001231


    good luck

    maybe this one is also help

    select t.id,t.START_DT,t.END_DT from t ,

    (
    select id,sum(sa) sa,sum(si) si,sum(ea) ea,sum(ei) ei from (
    select id,max(START_DT) sa,max(END_DT),0 ea,0 ei ea from t
    group by id
    union
    select id,0 sa,0 si,min(START_DT) si,min(END_DT) ei from t
    group by id)
    group by id

    ) s

    where (t.END_DT=s.ea or t.END_DT=s.ei
    or t.START_DT=s.sa or t.START_DT=s.si )and t.id=s.id

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •