Results 1 to 3 of 3

Thread: SQL Query

  1. #1
    Join Date
    May 2004
    Posts
    42

    Unanswered: SQL Query

    Hi
    I have some requirements like this

    Suppose a table Test(n Number(2)) is there which contains the values let's say

    1
    3
    12
    78
    100.

    I want the Range of missing Numbers through a query, i mean i want the output as

    2-2
    4-11
    13-77
    79-99

    Please help me...

    Thanks in advance.

    Regards
    Rasmi

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Welcome to analytic functions:
    Code:
    SQL> select s||'-'||e range
      2  from
      3  ( select n+1 s
      4    ,      (lead(n) over (order by n))-1 e
      5    from   test
      6  )
      7* where e is not null;
    
    RANGE
    ------
    2-2
    4-11
    13-77
    79-99

  3. #3
    Join Date
    Jan 2004
    Posts
    370
    Quote Originally Posted by andrewst
    Welcome to analytic functions:
    Code:
    SQL> select s||'-'||e range
      2  from
      3  ( select n+1 s
      4    ,      (lead(n) over (order by n))-1 e
      5    from   test
      6  )
      7* where e is not null;
    
    RANGE
    ------
    2-2
    4-11
    13-77
    79-99
    Hey, what a nice, elegant solution.
    Class, Tony, pure class.

Posting Permissions

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