6 Replies Latest reply on Aug 4, 2008 10:30 AM by Aketi Jyuuzou

    query only outer range

    474007
      Hi,
      I have a input table

      INPUT
      1
      2
      3
      5
      7
      8
      9
      11
      12
      13
      15
      17
      18
      20
      21
      22
      23

      and i want only the following numbers:

      OUPUT
      1
      3
      5
      7
      9
      11
      13
      15
      17
      18
      20
      23

      As you see i want only the number from the outer range. Can this be done with without plsql?

      Greetings
        • 1. Re: query only outer range
          170207
          Hi,
          SOTI@soti_9> WITH INPUT AS (
            2    SELECT 1 AS Val FROM Dual UNION ALL
            3    SELECT 2 FROM Dual UNION ALL
            4    SELECT 3 FROM Dual UNION ALL
            5    SELECT 5 FROM Dual UNION ALL
            6    SELECT 7 FROM Dual UNION ALL
            7    SELECT 8 FROM Dual UNION ALL
            8    SELECT 9 FROM Dual UNION ALL
            9    SELECT 11 FROM Dual UNION ALL
           10    SELECT 12 FROM Dual UNION ALL
           11    SELECT 13 FROM Dual UNION ALL
           12    SELECT 15 FROM Dual UNION ALL
           13    SELECT 17 FROM Dual UNION ALL
           14    SELECT 18 FROM Dual UNION ALL
           15    SELECT 20 FROM Dual UNION ALL
           16    SELECT 21 FROM Dual UNION ALL
           17    SELECT 22 FROM Dual UNION ALL
           18    SELECT 23 FROM Dual
           19  )
           20  SELECT Val
           21  FROM (
           22      SELECT I.Val,
           23        LAG(I.Val) OVER(ORDER BY I.Val) AS Prev_Val,
           24        LEAD(I.Val) OVER(ORDER BY I.Val) AS Next_Val
           25      FROM Input I
           26    )
           27  WHERE Prev_Val IS NULL
           28    OR Val - Prev_Val <> 1
           29    OR Next_Val IS NULL
           30    OR Next_Val - Val <> 1
           31  ORDER BY Val;
          
                 VAL
          ----------
                   1
                   3
                   5
                   7
                   9
                  11
                  13
                  15
                  17
                  18
                  20
                  23
          
          12 rows selected.
          Regards,
          Dima
          • 2. Re: query only outer range
            474007
            Thanks for fast response!
            • 3. Re: query only outer range
              Nicolas Gasparotto
              Just in addition,
              Prev_Val IS NULL
              [...]
              OR Next_Val IS NULL
              You can avoid these tests on null, by adding something else into LAG and LEAD functions :
              select *
              from (select val,
                           lead(val,1,val+2) over (order by val) val_next ,
                           lag(val,1,val-2) over (order by val) val_prev
                    from input)
              where val!=val_next-1 or val!=val_prev+1;
              Nicolas.
              • 4. Re: query only outer range
                646221
                what do you mean by outer range?

                RD
                • 5. Re: query only outer range
                  Aketi Jyuuzou
                  WITH INPUT AS (
                  SELECT 1 AS Val FROM Dual UNION ALL
                  SELECT 2 FROM Dual UNION ALL
                  SELECT 3 FROM Dual UNION ALL
                  SELECT 5 FROM Dual UNION ALL
                  SELECT 7 FROM Dual UNION ALL
                  SELECT 8 FROM Dual UNION ALL
                  SELECT 9 FROM Dual UNION ALL
                  SELECT 11 FROM Dual UNION ALL
                  SELECT 12 FROM Dual UNION ALL
                  SELECT 13 FROM Dual UNION ALL
                  SELECT 15 FROM Dual UNION ALL
                  SELECT 17 FROM Dual UNION ALL
                  SELECT 18 FROM Dual UNION ALL
                  SELECT 20 FROM Dual UNION ALL
                  SELECT 21 FROM Dual UNION ALL
                  SELECT 22 FROM Dual UNION ALL
                  SELECT 23 FROM Dual)
                  select Val
                  from (select Val,Lead(Val) over(order by Val) as LeadVal,
                        Lag(Val) over(order by Val) as LagVal
                          from INPUT)
                  where case when Val= all(LagVal+1,LeadVal-1)
                              then 1 else 0 end = 0;
                  VAL
                  ---
                    1
                    3
                    5
                    7
                    9
                  11
                  13
                  15
                  17
                  18
                  20
                  23
                  I have used is not true.

                  My old posts ;-)
                  Any Alternative SQL ?
                  What is the difference between "= NULL" and "IS NULL" in SQL?
                  • 6. Re: query only outer range
                    Aketi Jyuuzou
                    OOPS
                    We can use more simple one.
                    select Val
                    from (select Val,
                          count(*) over(order by Val
                                        range between 1 preceding
                                                  and 1 following) as cnt
                      from INPUT)
                    where cnt < 3
                    order by Val;