Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2010
    Posts
    2

    Exclamation Unanswered: Fetching discret data ranges

    Dears,
    I have the follwoing data in table "ITEMS"
    ITEM_CODE ITEM_NO
    ---------- --------------------
    SIM_CARD 00000000000000000010
    SIM_CARD 00000000000000000009
    SIM_CARD 00000000000000000005
    SIM_CARD 00000000000000000004
    SIM_CARD 00000000000000000003

    now I need a query to fetch the follwoing results

    ITEM_CODE Range_Start Range_End
    ---------- -------------------- --------------------
    SIM_CARD 00000000000000000003 00000000000000000005
    SIM_CARD 00000000000000000009 00000000000000000010

    Any suggestions?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    select ITEM_CODE, min(ITEM_NO), max(ITEM_NO)
    from items
    group by ITEM_CODE

  3. #3
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by andrewst View Post
    select ITEM_CODE, min(ITEM_NO), max(ITEM_NO)
    from items
    group by ITEM_CODE
    That will not give the desired results as it will collapse all intervals into a single row:
    Code:
    ITEM_CODE  MIN                  MAX
    ---------- -------------------- --------------------
    SIM_CARD   00000000000000000003 00000000000000000010
    which is not want mbhey wanted as far as I can tell.


    The best I can think of is something like this:
    Code:
    select *
    from (
      select item_code, 
             case 
               when start_marker = item_no then item_no 
               else null
             end as start_of_interval,
             case 
               when start_marker = item_no then lead(item_no) over (order by item_code, item_no) 
               else null
             end as end_of_interval
      from (
        select st.item_code, 
               st.item_no,
               case 
                 when prv.item_no is null then st.item_no
                 else null
               end as start_marker,
               case 
                 when nxt.item_no is null then st.item_no
                 else null
               end as end_marker
        from items st 
          left join items prv on to_number(st.item_no) - 1 = to_number(prv.item_no) AND st.item_code = prv.item_code
          left join items nxt on to_number(st.item_no) + 1 = to_number(nxt.item_no) AND st.item_code = nxt.item_code
        order by st.item_code, st.item_no
      )
      where start_marker is not null 
         or end_marker is not null
    )
    where start_of_interval is not null 
      and end_of_interval is not null
    ;

  4. #4
    Join Date
    Aug 2010
    Posts
    2

    Thumbs up Great solution

    Thanks Shammat .

Tags for this Thread

Posting Permissions

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