Get Gaps in a SQL Sequence

Question

Get Gaps in a SQL Sequence

Navigation

#1 by (3 votes)

3

Hello, I have a select today that returns the following result:

I make another select that returns a value, 5 for example. I would need to know which numbers are not between the number 1 to the number 5. In my case I would need the following result 2, 4, 5.

I would need help developing this logic.

sql sql-server

asked by anonymous 20.12.2018 / 17:05

1 answer

Click the Span (Trash icon) to trigger in my li events fadeOut and then remove IBM DFSORT - Retrieving only the current year for comparison

score 3 · Answer 1

You can use the WITH clause to generate the number sequence and compare it with the table in question:

WITH sequencias
  AS (SELECT 1 AS sequencia
      UNION ALL
      SELECT s.sequencia + 1 AS sequencia
        FROM sequencias s
       WHERE s.sequencia <= 5)
SELECT s.*
  FROM sequencias s
 WHERE NOT EXISTS(SELECT 1
                    FROM tabela t
                   WHERE t.nr_volume = s.sequencia)
 ORDER BY s.sequencia
OPTION(MAXRECURSION 0);

We have a great explanation of the WITH command in the question answer Using WITH AS command on Sql Server .