CODEWITHSUNDEEP
sqloracle-database
Nicole
Nicole

Reputation: 125

How to query for non-consecutive values?

I have a column of id: 1, 3, 4, 9, 10, 11 in the table called t_mark

How can I get the non-consecutive range? (e.g. [1, 3], [4, 9])

Upvotes: 0

Views: 2520

Answers (3)

Littlefoot
Littlefoot

Reputation: 142720

Alternatively, using LEAD analytic function, along with your fancy formatting. TEST CTE is what you already have; lines #9 onwards is what you need.

SQL> with test (col) as
  2    (select 1  from dual union all
  3     select 3  from dual union all
  4     select 4  from dual union all
  5     select 9  from dual union all
  6     select 10 from dual union all
  7     select 11 from dual
  8    ),
  9  temp as
 10    (select col,
 11            lead(col) over (order by col) lcol
 12     from test
 13    )
 14  select '[' || col ||' - '|| lcol ||']' result
 15  From temp
 16  where lcol - col > 1
 17  order by col;

RESULT
-------------------------------------------------------
[1 - 3]
[4 - 9]

SQL>

[EDIT: Adjusted so that you shouldn't have to think too much]

This is what you have:

SQL> select * From t_mark;

      M_ID
----------
         1
         3
         4
         9
        10
        11

6 rows selected.

This is what you need:

SQL> with temp as
  2    (select m_id,
  3            lead(m_id) over (order by m_id) lm_id
  4     from t_mark
  5    )
  6  select '[' || m_id ||' - '|| lm_id ||']' result
  7  From temp
  8  where lm_id - m_id > 1
  9  order by m_id;

RESULT
------------------------------------------------------------------
[1 - 3]
[4 - 9]

SQL>

Basically, you should learn how to use a CTE (common table expression, a.k.a. the with factoring clause).

Upvotes: 5

Theo
Theo

Reputation: 84

This should do the trick : 

WITH original_table(number_column) as (select 1  from dual union all
                                       select 3  from dual union all
                                       select 4  from dual union all
                                       select 9  from dual union all
                                       select 10 from dual union all
                                       select 11 from dual),
numbers AS (
    SELECT row_number() over (ORDER BY number_column ASC ) row_num,
           number_column
    FROM original_table
)

SELECT nb1.number_column AS lnumber,
       nb2.number_column AS rnumber

FROM numbers nb1

  INNER JOIN numbers nb2 ON nb1.row_num + 1 = nb2.row_num 
                        AND nb1.number_column + 1 < nb2.number_column

Result :

| LNUMBER | RNUMBER |
|---------|---------|
| 1       | 3       |
| 4       | 9       |

Link to the dbfiddle for testing

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Assuming that by "list" you mean a table with a column, then you can do this with lag():

select prev_number, number
from (select t.*, lag(number) over (order by number) as prev_number
      from t
     ) t
where prev_number <> number - 1;

Upvotes: 3

Related Questions