Mysql find id gaps in temporary table

Question

I need to find id gaps in a integer field of a temporary table. Due to a temporary table limitation I cannot open the table twice and join it with itself.

This is the example:

CREATE TEMPORARY TABLE tmp_table (
    id int(8) unsigned,
    PRIMARY KEY (id)
);

INSERT INTO tmp_table VALUES (1),(2),(4),(7),(10),(11),(13);

and here I want to find this resultset:

3, 5, [6], 8, [9], 12

[#]=optional

Possible duplicate of [How to find gaps in sequential numbering in mysql?](http://stackoverflow.com/questions/4340793/how-to-find-gaps-in-sequential-numbering-in-mysql) — mitkosoft, Feb 24 '16 at 12:22
@mitkosoft nice attemp, but my question is different becase I'm using temporary table. — Tobia, Feb 24 '16 at 12:25
I would like to know why I got a -1... unclear question? revenge? — Tobia, Feb 24 '16 at 15:57
Not from me. Useful question as many won't remember that restriction with temp tables. — Kickstart, Feb 24 '16 at 16:27

score 0 · Answer 1 · answered Feb 24 '16 at 15:04

A solution that works, but might be a better one.

This generates a range of numbers (so you will need to modify it for larger ranges - this copes up to 1000), and cross joins that range against the temp table. It finds the max and min ids from the temp table that are greater than or equal to or less or equal to (respectively) than generated value. Then in the HAVING clause check that the greater / less vales are not equal and also not null

SELECT 1 + units.i + tens.i * 10 + hundreds.i * 100 AS poss_num, 
        MAX(IF(tmp_table.id <= 1 + units.i + tens.i * 10 + hundreds.i * 100, tmp_table.id, NULL)) AS max_id, 
        MIN(IF(tmp_table.id >= 1 + units.i + tens.i * 10 + hundreds.i * 100, tmp_table.id, NULL)) AS min_id
FROM 
(
    SELECT 0 AS i 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
) units
CROSS JOIN
(
    SELECT 0 AS i 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
) tens
CROSS JOIN
(
    SELECT 0 AS i 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
) hundreds
CROSS JOIN tmp_table
GROUP BY poss_num
HAVING min_id != max_id
AND min_id IS NOT NULL
AND max_id IS NOT NULL

This is a very original solution, the main problem is that I have to predict id bounds and actually I'm workin with full usigned Int size (4294967295) — Tobia, Feb 24 '16 at 16:30
@Tobia - It is easy to expand the size of the range, but not sure if it will cause issues getting that big! You could also join against another random table that has the full range of ids (you could generate that in advance). It is avoiding the numbers above your max id that makes it complicated. — Kickstart, Feb 24 '16 at 16:41

Mysql find id gaps in temporary table

1 Answers1