MySQL get missing IDs from table
27
I have this table in MySQL, for example:
ID | Name
1 | Bob
4 | Adam
6 | Someguy
If you notice, there is no ID number (2, 3 and 5).
How can I write a query so that MySQL would answer the missing IDs only, in this case: "2,3,5" ?
mysql
asked Sep 7 '12 at 20:44
Reacen
87941330
add a comment |
27
I have this table in MySQL, for example:
ID | Name
1 | Bob
4 | Adam
6 | Someguy
If you notice, there is no ID number (2, 3 and 5).
How can I write a query so that MySQL would answer the missing IDs only, in this case: "2,3,5" ?
mysql
asked Sep 7 '12 at 20:44
Reacen
87941330
add a comment |
27
27
27
18
I have this table in MySQL, for example:
ID | Name
1 | Bob
4 | Adam
6 | Someguy
If you notice, there is no ID number (2, 3 and 5).
How can I write a query so that MySQL would answer the missing IDs only, in this case: "2,3,5" ?
mysql
asked Sep 7 '12 at 20:44
Reacen
87941330
I have this table in MySQL, for example:
ID | Name
1 | Bob
4 | Adam
6 | Someguy
If you notice, there is no ID number (2, 3 and 5).
How can I write a query so that MySQL would answer the missing IDs only, in this case: "2,3,5" ?
mysql
mysql
asked Sep 7 '12 at 20:44
Reacen
87941330
asked Sep 7 '12 at 20:44
Reacen
87941330
asked Sep 7 '12 at 20:44
Reacen
87941330
asked Sep 7 '12 at 20:44
Reacen
87941330
asked Sep 7 '12 at 20:44
Reacen
87941330
87941330
add a comment |
add a comment |
5 Answers
5
active
oldest
votes
23
SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
FROM testtable AS a, testtable AS b
WHERE a.id < b.id
GROUP BY a.id
HAVING start < MIN(b.id)
Hope this link also helps
http://www.codediesel.com/mysql/sequence-gaps-in-mysql/
answered Sep 7 '12 at 20:50
hagensoft
1,139813
1
working fine :) , but very slow on big data :(
– iiic
May 13 '16 at 12:17
This was genius! Thanks
– McRui
May 20 at 19:32
add a comment |
17
A more efficent query:
SELECT (t1.id + 1) as gap_starts_at,
(SELECT MIN(t3.id) -1 FROM my_table t3 WHERE t3.id > t1.id) as gap_ends_at
FROM my_table t1
WHERE NOT EXISTS (SELECT t2.id FROM my_table t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL
answered Dec 17 '14 at 7:45
Iván Pérez
1,01511031
Thanks Ivan. This runs so much quicker!
– MikeC
May 19 '16 at 1:24
This worked for me, except that it missed the initial gap starting at id=1
– egprentice
Oct 4 '17 at 14:56
add a comment |
3
To add a little to Ivan's answer, this version shows numbers missing at the beginning if 1 doesn't exist:
SELECT 1 as gap_starts_at,
(SELECT MIN(t4.id) -1 FROM testtable t4 WHERE t4.id > 1) as gap_ends_at
FROM testtable t5
WHERE NOT EXISTS (SELECT t6.id FROM testtable t6 WHERE t6.id = 1)
HAVING gap_ends_at IS NOT NULL limit 1
UNION
SELECT (t1.id + 1) as gap_starts_at,
(SELECT MIN(t3.id) -1 FROM testtable t3 WHERE t3.id > t1.id) as gap_ends_at
FROM testtable t1
WHERE NOT EXISTS (SELECT t2.id FROM testtable t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL;
answered Jun 30 '17 at 20:10
Jo.
62511116
Impressive.. great use of union and range
– Clain Dsilva
Apr 25 at 9:20
add a comment |
2
Above queries will give two columns so you can try this to get the missing numbers in a single column
select start from
(SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
FROM sequence AS a, sequence AS b
WHERE a.id < b.id
GROUP BY a.id
HAVING start < MIN(b.id)) b
UNION
select c.end from (SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
FROM sequence AS a, sequence AS b
WHERE a.id < b.id
GROUP BY a.id
HAVING start < MIN(b.id)) c order by start;
edited Feb 9 '17 at 8:42
Ish
1,627727
answered Aug 26 '15 at 12:48
Karan Kumar Mahto
667
With this one-column version, I get (for example)475,477,506,508,513but with the two-column version, it gets me the[475,475],[477,506],[508,513]which tells me I am missing numbers 475, 477-506, and 508-513.
– Jo.
Jun 30 '17 at 20:04
add a comment |
0
It would be far more efficient to get the start of the gap in one query and the end of the gap in one query.
I had 18M records and it took me less than a second each to get the two results. When I tried getting them together my query timed out after an hour.
Get the start of gap:
SELECT (t1.id + 1) as MissingID
FROM sequence t1
WHERE NOT EXISTS
(SELECT t2.id
FROM sequence t2
WHERE t2.id = t1.id + 1);
Get the end of gap:
SELECT (t1.id - 1) as MissingID
FROM sequence t1
WHERE NOT EXISTS
(SELECT t2.id
FROM sequence t2
WHERE t2.id = t1.id - 1);
edited Nov 23 at 2:11
Davіd
3,62541635
answered Nov 23 at 0:50
James G
11
1
Just a clarrification, I was suppose to have 18m records. It turns out I had only 3m records in my database and missed 15m records.
– James G
Nov 23 at 1:09
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f12325132%2fmysql-get-missing-ids-from-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
5 Answers
5
active
oldest
votes
5 Answers
5
active
oldest
votes
active
oldest
votes
active
oldest
votes
23
SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
FROM testtable AS a, testtable AS b
WHERE a.id < b.id
GROUP BY a.id
HAVING start < MIN(b.id)
Hope this link also helps
http://www.codediesel.com/mysql/sequence-gaps-in-mysql/
answered Sep 7 '12 at 20:50
hagensoft
1,139813
1
working fine :) , but very slow on big data :(
– iiic
May 13 '16 at 12:17
This was genius! Thanks
– McRui
May 20 at 19:32
add a comment |
23
SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
FROM testtable AS a, testtable AS b
WHERE a.id < b.id
GROUP BY a.id
HAVING start < MIN(b.id)
Hope this link also helps
http://www.codediesel.com/mysql/sequence-gaps-in-mysql/
answered Sep 7 '12 at 20:50
hagensoft
1,139813
1
working fine :) , but very slow on big data :(
– iiic
May 13 '16 at 12:17
This was genius! Thanks
– McRui
May 20 at 19:32
add a comment |
23
23
23
SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
FROM testtable AS a, testtable AS b
WHERE a.id < b.id
GROUP BY a.id
HAVING start < MIN(b.id)
Hope this link also helps
http://www.codediesel.com/mysql/sequence-gaps-in-mysql/
answered Sep 7 '12 at 20:50
hagensoft
1,139813
SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
FROM testtable AS a, testtable AS b
WHERE a.id < b.id
GROUP BY a.id
HAVING start < MIN(b.id)
Hope this link also helps
http://www.codediesel.com/mysql/sequence-gaps-in-mysql/
answered Sep 7 '12 at 20:50
hagensoft
1,139813
answered Sep 7 '12 at 20:50
hagensoft
1,139813
answered Sep 7 '12 at 20:50
hagensoft
1,139813
answered Sep 7 '12 at 20:50
hagensoft
1,139813
1,139813
1
working fine :) , but very slow on big data :(
– iiic
May 13 '16 at 12:17
This was genius! Thanks
– McRui
May 20 at 19:32
add a comment |
1
working fine :) , but very slow on big data :(
– iiic
May 13 '16 at 12:17
This was genius! Thanks
– McRui
May 20 at 19:32
1
1
working fine :) , but very slow on big data :(
– iiic
May 13 '16 at 12:17
working fine :) , but very slow on big data :(
– iiic
May 13 '16 at 12:17
This was genius! Thanks
– McRui
May 20 at 19:32
This was genius! Thanks
– McRui
May 20 at 19:32
add a comment |
17
A more efficent query:
SELECT (t1.id + 1) as gap_starts_at,
(SELECT MIN(t3.id) -1 FROM my_table t3 WHERE t3.id > t1.id) as gap_ends_at
FROM my_table t1
WHERE NOT EXISTS (SELECT t2.id FROM my_table t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL
answered Dec 17 '14 at 7:45
Iván Pérez
1,01511031
Thanks Ivan. This runs so much quicker!
– MikeC
May 19 '16 at 1:24
This worked for me, except that it missed the initial gap starting at id=1
– egprentice
Oct 4 '17 at 14:56
add a comment |
17
A more efficent query:
SELECT (t1.id + 1) as gap_starts_at,
(SELECT MIN(t3.id) -1 FROM my_table t3 WHERE t3.id > t1.id) as gap_ends_at
FROM my_table t1
WHERE NOT EXISTS (SELECT t2.id FROM my_table t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL
answered Dec 17 '14 at 7:45
Iván Pérez
1,01511031
Thanks Ivan. This runs so much quicker!
– MikeC
May 19 '16 at 1:24
This worked for me, except that it missed the initial gap starting at id=1
– egprentice
Oct 4 '17 at 14:56
add a comment |
17
17
17
A more efficent query:
SELECT (t1.id + 1) as gap_starts_at,
(SELECT MIN(t3.id) -1 FROM my_table t3 WHERE t3.id > t1.id) as gap_ends_at
FROM my_table t1
WHERE NOT EXISTS (SELECT t2.id FROM my_table t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL
answered Dec 17 '14 at 7:45
Iván Pérez
1,01511031
A more efficent query:
SELECT (t1.id + 1) as gap_starts_at,
(SELECT MIN(t3.id) -1 FROM my_table t3 WHERE t3.id > t1.id) as gap_ends_at
FROM my_table t1
WHERE NOT EXISTS (SELECT t2.id FROM my_table t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL
answered Dec 17 '14 at 7:45
Iván Pérez
1,01511031
edited Feb 2 at 13:11
answered Dec 17 '14 at 7:45
Iván Pérez
1,01511031
answered Dec 17 '14 at 7:45
Iván Pérez
1,01511031
answered Dec 17 '14 at 7:45
Iván Pérez
1,01511031
1,01511031
Thanks Ivan. This runs so much quicker!
– MikeC
May 19 '16 at 1:24
This worked for me, except that it missed the initial gap starting at id=1
– egprentice
Oct 4 '17 at 14:56
add a comment |
Thanks Ivan. This runs so much quicker!
– MikeC
May 19 '16 at 1:24
This worked for me, except that it missed the initial gap starting at id=1
– egprentice
Oct 4 '17 at 14:56
Thanks Ivan. This runs so much quicker!
– MikeC
May 19 '16 at 1:24
Thanks Ivan. This runs so much quicker!
– MikeC
May 19 '16 at 1:24
This worked for me, except that it missed the initial gap starting at id=1
– egprentice
Oct 4 '17 at 14:56
This worked for me, except that it missed the initial gap starting at id=1
– egprentice
Oct 4 '17 at 14:56
add a comment |
3
To add a little to Ivan's answer, this version shows numbers missing at the beginning if 1 doesn't exist:
SELECT 1 as gap_starts_at,
(SELECT MIN(t4.id) -1 FROM testtable t4 WHERE t4.id > 1) as gap_ends_at
FROM testtable t5
WHERE NOT EXISTS (SELECT t6.id FROM testtable t6 WHERE t6.id = 1)
HAVING gap_ends_at IS NOT NULL limit 1
UNION
SELECT (t1.id + 1) as gap_starts_at,
(SELECT MIN(t3.id) -1 FROM testtable t3 WHERE t3.id > t1.id) as gap_ends_at
FROM testtable t1
WHERE NOT EXISTS (SELECT t2.id FROM testtable t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL;
answered Jun 30 '17 at 20:10
Jo.
62511116
Impressive.. great use of union and range
– Clain Dsilva
Apr 25 at 9:20
add a comment |
3
To add a little to Ivan's answer, this version shows numbers missing at the beginning if 1 doesn't exist:
SELECT 1 as gap_starts_at,
(SELECT MIN(t4.id) -1 FROM testtable t4 WHERE t4.id > 1) as gap_ends_at
FROM testtable t5
WHERE NOT EXISTS (SELECT t6.id FROM testtable t6 WHERE t6.id = 1)
HAVING gap_ends_at IS NOT NULL limit 1
UNION
SELECT (t1.id + 1) as gap_starts_at,
(SELECT MIN(t3.id) -1 FROM testtable t3 WHERE t3.id > t1.id) as gap_ends_at
FROM testtable t1
WHERE NOT EXISTS (SELECT t2.id FROM testtable t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL;
answered Jun 30 '17 at 20:10
Jo.
62511116
Impressive.. great use of union and range
– Clain Dsilva
Apr 25 at 9:20
add a comment |
3
3
3
To add a little to Ivan's answer, this version shows numbers missing at the beginning if 1 doesn't exist:
SELECT 1 as gap_starts_at,
(SELECT MIN(t4.id) -1 FROM testtable t4 WHERE t4.id > 1) as gap_ends_at
FROM testtable t5
WHERE NOT EXISTS (SELECT t6.id FROM testtable t6 WHERE t6.id = 1)
HAVING gap_ends_at IS NOT NULL limit 1
UNION
SELECT (t1.id + 1) as gap_starts_at,
(SELECT MIN(t3.id) -1 FROM testtable t3 WHERE t3.id > t1.id) as gap_ends_at
FROM testtable t1
WHERE NOT EXISTS (SELECT t2.id FROM testtable t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL;
answered Jun 30 '17 at 20:10
Jo.
62511116
To add a little to Ivan's answer, this version shows numbers missing at the beginning if 1 doesn't exist:
SELECT 1 as gap_starts_at,
(SELECT MIN(t4.id) -1 FROM testtable t4 WHERE t4.id > 1) as gap_ends_at
FROM testtable t5
WHERE NOT EXISTS (SELECT t6.id FROM testtable t6 WHERE t6.id = 1)
HAVING gap_ends_at IS NOT NULL limit 1
UNION
SELECT (t1.id + 1) as gap_starts_at,
(SELECT MIN(t3.id) -1 FROM testtable t3 WHERE t3.id > t1.id) as gap_ends_at
FROM testtable t1
WHERE NOT EXISTS (SELECT t2.id FROM testtable t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL;
answered Jun 30 '17 at 20:10
Jo.
62511116
edited Jun 30 '17 at 20:16
answered Jun 30 '17 at 20:10
Jo.
62511116
answered Jun 30 '17 at 20:10
Jo.
62511116
answered Jun 30 '17 at 20:10
Jo.
62511116
62511116
Impressive.. great use of union and range
– Clain Dsilva
Apr 25 at 9:20
add a comment |
Impressive.. great use of union and range
– Clain Dsilva
Apr 25 at 9:20
Impressive.. great use of union and range
– Clain Dsilva
Apr 25 at 9:20
Impressive.. great use of union and range
– Clain Dsilva
Apr 25 at 9:20
add a comment |
2
Above queries will give two columns so you can try this to get the missing numbers in a single column
select start from
(SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
FROM sequence AS a, sequence AS b
WHERE a.id < b.id
GROUP BY a.id
HAVING start < MIN(b.id)) b
UNION
select c.end from (SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
FROM sequence AS a, sequence AS b
WHERE a.id < b.id
GROUP BY a.id
HAVING start < MIN(b.id)) c order by start;
edited Feb 9 '17 at 8:42
Ish
1,627727
answered Aug 26 '15 at 12:48
Karan Kumar Mahto
667
With this one-column version, I get (for example)475,477,506,508,513but with the two-column version, it gets me the[475,475],[477,506],[508,513]which tells me I am missing numbers 475, 477-506, and 508-513.
– Jo.
Jun 30 '17 at 20:04
add a comment |
2
Above queries will give two columns so you can try this to get the missing numbers in a single column
select start from
(SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
FROM sequence AS a, sequence AS b
WHERE a.id < b.id
GROUP BY a.id
HAVING start < MIN(b.id)) b
UNION
select c.end from (SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
FROM sequence AS a, sequence AS b
WHERE a.id < b.id
GROUP BY a.id
HAVING start < MIN(b.id)) c order by start;
edited Feb 9 '17 at 8:42
Ish
1,627727
answered Aug 26 '15 at 12:48
Karan Kumar Mahto
667
With this one-column version, I get (for example)475,477,506,508,513but with the two-column version, it gets me the[475,475],[477,506],[508,513]which tells me I am missing numbers 475, 477-506, and 508-513.
– Jo.
Jun 30 '17 at 20:04
add a comment |
2
2
2
Above queries will give two columns so you can try this to get the missing numbers in a single column
select start from
(SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
FROM sequence AS a, sequence AS b
WHERE a.id < b.id
GROUP BY a.id
HAVING start < MIN(b.id)) b
UNION
select c.end from (SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
FROM sequence AS a, sequence AS b
WHERE a.id < b.id
GROUP BY a.id
HAVING start < MIN(b.id)) c order by start;
edited Feb 9 '17 at 8:42
Ish
1,627727
answered Aug 26 '15 at 12:48
Karan Kumar Mahto
667
Above queries will give two columns so you can try this to get the missing numbers in a single column
select start from
(SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
FROM sequence AS a, sequence AS b
WHERE a.id < b.id
GROUP BY a.id
HAVING start < MIN(b.id)) b
UNION
select c.end from (SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
FROM sequence AS a, sequence AS b
WHERE a.id < b.id
GROUP BY a.id
HAVING start < MIN(b.id)) c order by start;
edited Feb 9 '17 at 8:42
Ish
1,627727
answered Aug 26 '15 at 12:48
Karan Kumar Mahto
667
edited Feb 9 '17 at 8:42
Ish
1,627727
edited Feb 9 '17 at 8:42
Ish
1,627727
edited Feb 9 '17 at 8:42
Ish
1,627727
1,627727
answered Aug 26 '15 at 12:48
Karan Kumar Mahto
667
answered Aug 26 '15 at 12:48
Karan Kumar Mahto
667
answered Aug 26 '15 at 12:48
Karan Kumar Mahto
667
667
With this one-column version, I get (for example)475,477,506,508,513but with the two-column version, it gets me the[475,475],[477,506],[508,513]which tells me I am missing numbers 475, 477-506, and 508-513.
– Jo.
Jun 30 '17 at 20:04
add a comment |
With this one-column version, I get (for example)475,477,506,508,513but with the two-column version, it gets me the[475,475],[477,506],[508,513]which tells me I am missing numbers 475, 477-506, and 508-513.
– Jo.
Jun 30 '17 at 20:04
With this one-column version, I get (for example)
475, 477, 506, 508, 513 but with the two-column version, it gets me the [475,475], [477,506], [508,513] which tells me I am missing numbers 475, 477-506, and 508-513.– Jo.
Jun 30 '17 at 20:04
With this one-column version, I get (for example)
475, 477, 506, 508, 513 but with the two-column version, it gets me the [475,475], [477,506], [508,513] which tells me I am missing numbers 475, 477-506, and 508-513.– Jo.
Jun 30 '17 at 20:04
add a comment |
0
It would be far more efficient to get the start of the gap in one query and the end of the gap in one query.
I had 18M records and it took me less than a second each to get the two results. When I tried getting them together my query timed out after an hour.
Get the start of gap:
SELECT (t1.id + 1) as MissingID
FROM sequence t1
WHERE NOT EXISTS
(SELECT t2.id
FROM sequence t2
WHERE t2.id = t1.id + 1);
Get the end of gap:
SELECT (t1.id - 1) as MissingID
FROM sequence t1
WHERE NOT EXISTS
(SELECT t2.id
FROM sequence t2
WHERE t2.id = t1.id - 1);
edited Nov 23 at 2:11
Davіd
3,62541635
answered Nov 23 at 0:50
James G
11
1
Just a clarrification, I was suppose to have 18m records. It turns out I had only 3m records in my database and missed 15m records.
– James G
Nov 23 at 1:09
add a comment |
0
It would be far more efficient to get the start of the gap in one query and the end of the gap in one query.
I had 18M records and it took me less than a second each to get the two results. When I tried getting them together my query timed out after an hour.
Get the start of gap:
SELECT (t1.id + 1) as MissingID
FROM sequence t1
WHERE NOT EXISTS
(SELECT t2.id
FROM sequence t2
WHERE t2.id = t1.id + 1);
Get the end of gap:
SELECT (t1.id - 1) as MissingID
FROM sequence t1
WHERE NOT EXISTS
(SELECT t2.id
FROM sequence t2
WHERE t2.id = t1.id - 1);
edited Nov 23 at 2:11
Davіd
3,62541635
answered Nov 23 at 0:50
James G
11
1
Just a clarrification, I was suppose to have 18m records. It turns out I had only 3m records in my database and missed 15m records.
– James G
Nov 23 at 1:09
add a comment |
0
0
0
It would be far more efficient to get the start of the gap in one query and the end of the gap in one query.
I had 18M records and it took me less than a second each to get the two results. When I tried getting them together my query timed out after an hour.
Get the start of gap:
SELECT (t1.id + 1) as MissingID
FROM sequence t1
WHERE NOT EXISTS
(SELECT t2.id
FROM sequence t2
WHERE t2.id = t1.id + 1);
Get the end of gap:
SELECT (t1.id - 1) as MissingID
FROM sequence t1
WHERE NOT EXISTS
(SELECT t2.id
FROM sequence t2
WHERE t2.id = t1.id - 1);
edited Nov 23 at 2:11
Davіd
3,62541635
answered Nov 23 at 0:50
James G
11
It would be far more efficient to get the start of the gap in one query and the end of the gap in one query.
I had 18M records and it took me less than a second each to get the two results. When I tried getting them together my query timed out after an hour.
Get the start of gap:
SELECT (t1.id + 1) as MissingID
FROM sequence t1
WHERE NOT EXISTS
(SELECT t2.id
FROM sequence t2
WHERE t2.id = t1.id + 1);
Get the end of gap:
SELECT (t1.id - 1) as MissingID
FROM sequence t1
WHERE NOT EXISTS
(SELECT t2.id
FROM sequence t2
WHERE t2.id = t1.id - 1);
edited Nov 23 at 2:11
Davіd
3,62541635
answered Nov 23 at 0:50
James G
11
edited Nov 23 at 2:11
Davіd
3,62541635
edited Nov 23 at 2:11
Davіd
3,62541635
edited Nov 23 at 2:11
Davіd
3,62541635
3,62541635
answered Nov 23 at 0:50
James G
11
answered Nov 23 at 0:50
James G
11
answered Nov 23 at 0:50
James G
11
11
1
Just a clarrification, I was suppose to have 18m records. It turns out I had only 3m records in my database and missed 15m records.
– James G
Nov 23 at 1:09
add a comment |
1
Just a clarrification, I was suppose to have 18m records. It turns out I had only 3m records in my database and missed 15m records.
– James G
Nov 23 at 1:09
1
1
Just a clarrification, I was suppose to have 18m records. It turns out I had only 3m records in my database and missed 15m records.
– James G
Nov 23 at 1:09
Just a clarrification, I was suppose to have 18m records. It turns out I had only 3m records in my database and missed 15m records.
– James G
Nov 23 at 1:09
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f12325132%2fmysql-get-missing-ids-from-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
