Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2003
    Posts
    12

    Unanswered: how to create query ??????????

    create table qq
    (qty number,
    start_no number,
    end_no number);

    insert into qq values (1,1,100);
    insert into qq values (1,101,200);
    insert into qq values (1,201,300);
    insert into qq values (1,5001,5100);
    insert into qq values (1,7001,7100);
    insert into qq values (1,7101,7200);

    now
    i have a table with following data
    qty start_no end_no
    1 1 100
    1 101 200
    1 201 300
    1 5001 5100
    1 7001 7100
    1 7101 7200


    i used query like
    select sum(qty),min(start_no),max(end_no) from qq;

    it show
    6 1 7200

    BUT I WANT
    3 1 300
    1 5001 5100
    2 7001 7200

    PLZ HELP ME????????????????

    THANKS A LOT
    kuljeet pal singh

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: how to create query ??????????

    Please explain what that 3-line result is supposed to represent? I can't see it myself.

  3. #3
    Join Date
    Sep 2003
    Posts
    12
    I WANT
    qty start_no end_no
    3 1 300
    1 5001 5100
    2 7001 7200
    if start_no and end_no in particule sequence then group it
    else
    create another group of another series

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What "particule sequence" are you refering to? Please be more clear!

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    If you mean, values in range 1-1000, 1001-2000 etc. then this would do it:

    select sum(qty),min(start_no),max(end_no)
    from qq
    group by trunc(start_no/1000);

  6. #6
    Join Date
    Sep 2003
    Posts
    12
    like

    start_no-1=last_no of previous record;
    if equal then it is a series

    ****LIKE THIS *******
    particuler sequence is like
    start_no last_no
    1 100
    101 200
    201 300

    (this is a sequence from 1 to 300) NO BREAK IN SEQUENCE
    and
    another
    5001 5100
    5101 5200
    this sequence is start from 5001 to 5200

    so two group is created
    qty start_no end_no
    3 1 300
    2 5001 5200

  7. #7
    Join Date
    Sep 2003
    Posts
    12
    u r query will run but not according to my requirement
    if another value is inserted
    like
    insert into qq values(1,5501,5600);
    NOW data in table is

    QTY START_NO END_NO
    ----- ---------- ----------
    1 1 100
    1 101 200
    1 201 300
    1 5001 5100
    1 7001 7100
    1 7101 7200
    1 5501 5600
    now i want

    3 1 300
    1 5001 5100
    2 7001 7200
    1 5501 5600 <----because it will not in series
    if start_no is 5101 in place of 5501
    then it will group in series of 5001
    and 5001 series return qty 2 in place of 1

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    That's a lot harder, but I think this does it right:
    Code:
    SQL> select sum(qty),min(start_no),max(end_no)
      2  from qq,
      3  ( select range_start,
      4           nvl((lead(range_start) over (order by range_start))-1,999999999) range_end
      5    from
      6    ( select decode(ld,start_no-1,null,start_no) range_start
      7      from
      8      ( select start_no, end_no, lag(end_no) over (order by start_no) ld
      9        from qq
     10      )
     11      where decode(ld,start_no-1,null,start_no) is not null
     12    )
     13  ) ranges
     14  where start_no between ranges.range_start and ranges.range_end
     15  group by ranges.range_start;
    
      SUM(QTY) MIN(START_NO) MAX(END_NO)
    ---------- ------------- -----------
             3             1         300
             1          5001        5100
             1          5501        5600
             2          7001        7200
    To see how it works, start with the innermost query and work outwards.

    (PS Why do you type "u r query" instead of "your query"? It only saves
    1 keystroke, but it confuses the hell out of me!)

  9. #9
    Join Date
    Sep 2003
    Posts
    12

    Talking

    thanks a lot for your reply.
    it will help me.

    kuljeet pal singh

Posting Permissions

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