Using SQL to get the minimal unused number from a set of continuously increasing numbers

Question

For example: in my db, there are 10 records: 500, 501, 502, 503, 504, 506, 507, 508, 509, 510. I want to get 505 which is not used. (The start number is 500).

If 501, 502, 503 in db, I want to get 500.

I use MySQL.

I could post the solution but only for MS SQL Server if interested. Let me know — Leo, Dec 10 '13 at 04:08
I see, thanks, but i do not want to write procedure. Just one sql statement. — Yifan Wang, Dec 10 '13 at 04:56

score 2 · Answer 1 · answered Dec 10 '13 at 04:19

2

if you can get know the total sum (i.e., total sum of 500 to 510 without missing number) then, try

SELECT <total sum> - SUM(<field name>) FROM <table name>;

Eg: for db 1,2,3,4, 5, 7, 8, 9, 10 code is:

SELECT 55 - SUM(id) FROM 'a'

answered Dec 10 '13 at 04:19

omrehman

107
3
13

1

What if there is more than one missing record? – Joel Coehoorn Dec 10 '13 at 04:42

Joel Coehoorn · Answer 2 · 2013-12-10T04:39:34.920

The general solution to this problem is to generate a list of desired numbers, and then do an exclusion join (left outer join where a required column in the additional table is null) from that list to your table to look for records that are missing.

This may be a bit confusing, as the code to generate a sequence on the fly is a bit to take in the first time:

SELECT
    (HUNDREDS.SeqValue + TENS.SeqValue + ONES.SeqValue) SeqValue
FROM
 /* Generate sequence. Need one set of digits per power of 10 */
(      
    SELECT 0  SeqValue
    UNION ALL
    SELECT 1 SeqValue
    UNION ALL
    SELECT 2 SeqValue
    UNION ALL
    SELECT 3 SeqValue
    UNION ALL
    SELECT 4 SeqValue
    UNION ALL
    SELECT 5 SeqValue
    UNION ALL
    SELECT 6 SeqValue
    UNION ALL
    SELECT 7 SeqValue
    UNION ALL
    SELECT 8 SeqValue
    UNION ALL
    SELECT 9 SeqValue
 ) ONES
CROSS JOIN
 (
    SELECT 0 SeqValue
    UNION ALL
    SELECT 10 SeqValue
    UNION ALL
    SELECT 20 SeqValue
    UNION ALL
    SELECT 30 SeqValue
    UNION ALL
    SELECT 40 SeqValue
    UNION ALL
    SELECT 50 SeqValue
    UNION ALL
    SELECT 60 SeqValue
    UNION ALL
    SELECT 70 SeqValue
    UNION ALL
    SELECT 80 SeqValue
    UNION ALL
    SELECT 90 SeqValue
 ) TENS
CROSS JOIN
 (
    SELECT 0 SeqValue
    UNION ALL
    SELECT 100 SeqValue
    UNION ALL
    SELECT 200 SeqValue
    UNION ALL
    SELECT 300 SeqValue
    UNION ALL
    SELECT 400 SeqValue
    UNION ALL
    SELECT 500 SeqValue
    UNION ALL
    SELECT 600 SeqValue
    UNION ALL
    SELECT 700 SeqValue
    UNION ALL
    SELECT 800 SeqValue
    UNION ALL
    SELECT 900 SeqValue
 ) HUNDREDS
/* done generating sequence */
LEFT JOIN `MyTable` ON `MyTable`.`id` = HUNDREDS.SeqValue + TENS.SeqValue + ONES.SeqValue
WHERE HUNDREDS.SeqValue + TENS.SeqValue + ONES.SeqValue >= 500
    AND HUNDREDS.SeqValue + TENS.SeqValue + ONES.SeqValue < (SELECT MAX(id) FROM `MyTable`)
    AND `MyTable`.`id` IS NULL

You may need to add a thousands or ten thousands digit, depending on the size of your table. If this is something you'll do often, you can use this code or a simple loop to insert the digits into a real table somewhere.

FWIW, Sql Server, Oracle, and PostGRE all allow code more like this (more brief, readable):

with digits as (
    select '0' "digit"
    union
    select '1'
    union
    select '2'
    union
    select '3'
    union
    select '4'
    union
    select '5'
    union
    select '6'
    union
    select '7'
    union
    select '8'
    union
    select '9'
)
select cast(hundreds.digit + tens.digit + ones.digit as integer) "value"
from digits ones
cross join digits tens
cross join digits hundreds

It's not often I get to use a CROSS JOIN :)

score 1 · Answer 3 · answered Dec 10 '13 at 04:48

**

MAGIC

**

SET @RANK = 0;

SELECT 
    (COLUMN_NAME - 1) AS COLUMN_NAME 
FROM 
    (
        SELECT COLUMN_NAME  
        FROM TEST 
        ORDER BY COLUMN_NAME
    ) AS ORDERED
GROUP BY 
    ORDERED.COLUMN_NAME- (@RANK:=@RANK+1)
HAVING 
    (COLUMN_NAME- (@RANK:=@RANK+1)) <> MIN(COLUMN_NAME)
LIMIT 1;

**

FULL EXAMPLE

**

CREATE TEMPORARY TABLE TEST (
    COLUMN_NAME INTEGER
);

INSERT INTO TEST (COLUMN_NAME)  VALUES 
    (500),  (501),
    (502),  (503),
    (504), 
    (506),  (507),
    (508),  (509),
    (510); 

SET @RANK = 0;

SELECT 
    (COLUMN_NAME - 1) AS COLUMN_NAME 
FROM 
    (
        SELECT COLUMN_NAME  
        FROM TEST 
        ORDER BY COLUMN_NAME
    ) AS ORDERED
GROUP BY 
    ORDERED.COLUMN_NAME- (@RANK:=@RANK+1)
HAVING 
    (COLUMN_NAME- (@RANK:=@RANK+1)) <> MIN(COLUMN_NAME)
LIMIT 1;


DROP TABLE TEST;

score 0 · Accepted Answer · answered Dec 10 '13 at 05:17

0

Another way to do it

SELECT number + 1 first_unused
  FROM 
(
  SELECT 499 number
   UNION ALL
  SELECT number 
    FROM table1
   WHERE number >= 500
) t
 WHERE NOT EXISTS
(
  SELECT *
    FROM table1
   WHERE number = t.number + 1
)
LIMIT 1

Here is SQLFiddle demo

answered Dec 10 '13 at 05:17

peterm

91,357
15
148
157

yeah, it's a similar solution of mine, thanks a lot. – Yifan Wang Dec 10 '13 at 06:54

score 0 · Answer 5 · answered Dec 10 '13 at 05:45

0

I got one solution of myself.

select min(a.id)+1 from ((select 499 as id) union (select id from table)) as a where NOT EXISTS (select id from table where id = a.id + 1)

answered Dec 10 '13 at 05:45

Yifan Wang

504
6
13

Using SQL to get the minimal unused number from a set of continuously increasing numbers

5 Answers5

MAGIC

FULL EXAMPLE