CODEWITHSUNDEEP
sqloracle11g
brainmassage
brainmassage

Reputation: 1254

How can I find unoccupied id numbers in a table?

In my table I want to see a list of unoccupied id numbers in a certain range.

For example there are 10 records in my table with id's: "2,3,4,5,10,12,16,18,21,22" and say that I want to see available ones between 1 and 25. So I want to see a list like:

1,6,7,89,11,13,14,15,17,19,20,23,24,25

How should I write my sql query?

Upvotes: 0

Views: 117

Answers (4)

Lalit Kumar B
Lalit Kumar B

Reputation: 49092

You need LISTAGG to get the output in a single row.

SQL> WITH DATA1 AS(
  2  SELECT LEVEL rn FROM dual CONNECT BY LEVEL <=25
  3  ),
  4  data2 AS(
  5  SELECT 2 num FROM dual UNION ALL
  6  SELECT 3 FROM dual UNION ALL
  7  SELECT 4 from dual union all
  8  SELECT 5 FROM dual UNION ALL
  9  SELECT 10 FROM dual UNION ALL
 10  SELECT 12 from dual union all
 11  SELECT 16 from dual union all
 12  SELECT 18 FROM dual UNION ALL
 13  SELECT 21 FROM dual UNION ALL
 14  SELECT 22 FROM dual)
 15  SELECT listagg(rn, ',')
 16  WITHIN GROUP (ORDER BY rn) num_list FROM data1
 17  WHERE rn NOT IN(SELECT num FROM data2)
 18  /

NUM_LIST
----------------------------------------------------
1,6,7,8,9,11,13,14,15,17,19,20,23,24,25

SQL>

Upvotes: 0

StackTrace
StackTrace

Reputation: 9416

You can try using the "NOT IN" clause:

select
    u1.user_id + 1 as start
from users as u1
  left outer join users as u2 on u1.user_id + 1 = u2.id
where
    u2.id is null

see also SQL query to find Missing sequence numbers

Upvotes: 0

Sam
Sam

Reputation: 4484

Let's say you a #numbers table with three numbers - 

CREATE TABLE #numbers (num INT)

INSERT INTO #numbers (num)
SELECT 1
UNION
SELECT 3
UNION
SELECT 6

Now, you can use CTE to generate numbers recursively from 1-25 and deselect those which are in your #numbers table in the WHERE clause - 

;WITH n(n) AS
(
    SELECT 1
    UNION ALL
    SELECT n+1 FROM n WHERE n < 25
)
SELECT n FROM n 
WHERE n NOT IN (select num from #numbers)
ORDER BY n
OPTION (MAXRECURSION 25);

Upvotes: 0

overflowed
overflowed

Reputation: 1838

Select the numbers form 1 to 25 and show only those that are not in your table

select n from
( select rownum n from dual connect by level <= 25)
where n not in (select id from table);

Upvotes: 2

Related Questions