Mixing
Finding only missing sequence number with id
Find the missing sequence. Table test_number contains the sequence for each id. Table test_number_min_max contains the minimum and maximum number for each id. We need to find the missing number between the minimum and maximum number for each id.
I have two Tables
CREATE TABLE test_number(id NUMBER,SEQ NUMBER,text VARCHAR2(5)) ;
INSERT INTO test_number VALUES(1,1,'AA');
INSERT INTO test_number VALUES(1,3,'CC');
INSERT INTO test_number VALUES(1,4,'DD');
INSERT INTO test_number VALUES(1,5,'EE');
INSERT INTO test_number VALUES(1,6,'FF');
INSERT INTO test_number VALUES(1,7,'GG');
INSERT INTO test_number VALUES(1,8,'HH');
INSERT INTO test_number VALUES(1,10,'JJ');
INSERT INTO test_number VALUES(2,1,'KK');
INSERT INTO test_number VALUES(2,2,'LL');
INSERT INTO test_number VALUES(2,3,'MM');
INSERT INTO test_number VALUES(2,4,'NN');
INSERT INTO test_number VALUES(2,6,'PP');
INSERT INTO test_number VALUES(2,7,'QQ');
INSERT INTO test_number VALUES(3,1,'TT');
INSERT INTO test_number VALUES(3,4,'ZZ');
INSERT INTO test_number VALUES(3,5,'XX');
create tabel test_number_min_max(id number,mn number,mx number);
INSERT INTO test_number_min_max VALUES(1,1,12);
INSERT INTO test_number_min_max VALUES(2,1,9);
INSERT INTO test_number_min_max VALUES(3,1,5);
Below Query works in oracle but I want query which executes in SQL serevr2008.
SELECT r id,rn seq FROM(SELECT ROWNUM rn FROM all_objects WHER ROWNUM <13),
(SELECT ROWNUM r FROM all_objects
WHERE ROWNUM <4),test_number_min_max m
WHERE r=id
AND rn >= mn
AND rn <= mx
AND(r,rn) NOT IN
(SELECT id,seq FROM test_number)
sql gaps-and-islands
edited Nov 22 '18 at 4:19
Cœur
18.4k9109148
asked Nov 1 '14 at 10:35
SandeshSandesh
846
add a comment |
Find the missing sequence. Table test_number contains the sequence for each id. Table test_number_min_max contains the minimum and maximum number for each id. We need to find the missing number between the minimum and maximum number for each id.
I have two Tables
CREATE TABLE test_number(id NUMBER,SEQ NUMBER,text VARCHAR2(5)) ;
INSERT INTO test_number VALUES(1,1,'AA');
INSERT INTO test_number VALUES(1,3,'CC');
INSERT INTO test_number VALUES(1,4,'DD');
INSERT INTO test_number VALUES(1,5,'EE');
INSERT INTO test_number VALUES(1,6,'FF');
INSERT INTO test_number VALUES(1,7,'GG');
INSERT INTO test_number VALUES(1,8,'HH');
INSERT INTO test_number VALUES(1,10,'JJ');
INSERT INTO test_number VALUES(2,1,'KK');
INSERT INTO test_number VALUES(2,2,'LL');
INSERT INTO test_number VALUES(2,3,'MM');
INSERT INTO test_number VALUES(2,4,'NN');
INSERT INTO test_number VALUES(2,6,'PP');
INSERT INTO test_number VALUES(2,7,'QQ');
INSERT INTO test_number VALUES(3,1,'TT');
INSERT INTO test_number VALUES(3,4,'ZZ');
INSERT INTO test_number VALUES(3,5,'XX');
create tabel test_number_min_max(id number,mn number,mx number);
INSERT INTO test_number_min_max VALUES(1,1,12);
INSERT INTO test_number_min_max VALUES(2,1,9);
INSERT INTO test_number_min_max VALUES(3,1,5);
Below Query works in oracle but I want query which executes in SQL serevr2008.
SELECT r id,rn seq FROM(SELECT ROWNUM rn FROM all_objects WHER ROWNUM <13),
(SELECT ROWNUM r FROM all_objects
WHERE ROWNUM <4),test_number_min_max m
WHERE r=id
AND rn >= mn
AND rn <= mx
AND(r,rn) NOT IN
(SELECT id,seq FROM test_number)
sql gaps-and-islands
edited Nov 22 '18 at 4:19
Cœur
18.4k9109148
asked Nov 1 '14 at 10:35
SandeshSandesh
846
Can you edit your question and show what you want as output?
– Gordon Linoff
Nov 1 '14 at 12:39
add a comment |
Find the missing sequence. Table test_number contains the sequence for each id. Table test_number_min_max contains the minimum and maximum number for each id. We need to find the missing number between the minimum and maximum number for each id.
I have two Tables
CREATE TABLE test_number(id NUMBER,SEQ NUMBER,text VARCHAR2(5)) ;
INSERT INTO test_number VALUES(1,1,'AA');
INSERT INTO test_number VALUES(1,3,'CC');
INSERT INTO test_number VALUES(1,4,'DD');
INSERT INTO test_number VALUES(1,5,'EE');
INSERT INTO test_number VALUES(1,6,'FF');
INSERT INTO test_number VALUES(1,7,'GG');
INSERT INTO test_number VALUES(1,8,'HH');
INSERT INTO test_number VALUES(1,10,'JJ');
INSERT INTO test_number VALUES(2,1,'KK');
INSERT INTO test_number VALUES(2,2,'LL');
INSERT INTO test_number VALUES(2,3,'MM');
INSERT INTO test_number VALUES(2,4,'NN');
INSERT INTO test_number VALUES(2,6,'PP');
INSERT INTO test_number VALUES(2,7,'QQ');
INSERT INTO test_number VALUES(3,1,'TT');
INSERT INTO test_number VALUES(3,4,'ZZ');
INSERT INTO test_number VALUES(3,5,'XX');
create tabel test_number_min_max(id number,mn number,mx number);
INSERT INTO test_number_min_max VALUES(1,1,12);
INSERT INTO test_number_min_max VALUES(2,1,9);
INSERT INTO test_number_min_max VALUES(3,1,5);
Below Query works in oracle but I want query which executes in SQL serevr2008.
SELECT r id,rn seq FROM(SELECT ROWNUM rn FROM all_objects WHER ROWNUM <13),
(SELECT ROWNUM r FROM all_objects
WHERE ROWNUM <4),test_number_min_max m
WHERE r=id
AND rn >= mn
AND rn <= mx
AND(r,rn) NOT IN
(SELECT id,seq FROM test_number)
sql gaps-and-islands
edited Nov 22 '18 at 4:19
Cœur
18.4k9109148
asked Nov 1 '14 at 10:35
SandeshSandesh
846
Find the missing sequence. Table test_number contains the sequence for each id. Table test_number_min_max contains the minimum and maximum number for each id. We need to find the missing number between the minimum and maximum number for each id.
I have two Tables
CREATE TABLE test_number(id NUMBER,SEQ NUMBER,text VARCHAR2(5)) ;
INSERT INTO test_number VALUES(1,1,'AA');
INSERT INTO test_number VALUES(1,3,'CC');
INSERT INTO test_number VALUES(1,4,'DD');
INSERT INTO test_number VALUES(1,5,'EE');
INSERT INTO test_number VALUES(1,6,'FF');
INSERT INTO test_number VALUES(1,7,'GG');
INSERT INTO test_number VALUES(1,8,'HH');
INSERT INTO test_number VALUES(1,10,'JJ');
INSERT INTO test_number VALUES(2,1,'KK');
INSERT INTO test_number VALUES(2,2,'LL');
INSERT INTO test_number VALUES(2,3,'MM');
INSERT INTO test_number VALUES(2,4,'NN');
INSERT INTO test_number VALUES(2,6,'PP');
INSERT INTO test_number VALUES(2,7,'QQ');
INSERT INTO test_number VALUES(3,1,'TT');
INSERT INTO test_number VALUES(3,4,'ZZ');
INSERT INTO test_number VALUES(3,5,'XX');
create tabel test_number_min_max(id number,mn number,mx number);
INSERT INTO test_number_min_max VALUES(1,1,12);
INSERT INTO test_number_min_max VALUES(2,1,9);
INSERT INTO test_number_min_max VALUES(3,1,5);
Below Query works in oracle but I want query which executes in SQL serevr2008.
SELECT r id,rn seq FROM(SELECT ROWNUM rn FROM all_objects WHER ROWNUM <13),
(SELECT ROWNUM r FROM all_objects
WHERE ROWNUM <4),test_number_min_max m
WHERE r=id
AND rn >= mn
AND rn <= mx
AND(r,rn) NOT IN
(SELECT id,seq FROM test_number)
sql gaps-and-islands
sql gaps-and-islands
edited Nov 22 '18 at 4:19
Cœur
18.4k9109148
asked Nov 1 '14 at 10:35
SandeshSandesh
846
edited Nov 22 '18 at 4:19
Cœur
18.4k9109148
asked Nov 1 '14 at 10:35
SandeshSandesh
846
edited Nov 22 '18 at 4:19
Cœur
18.4k9109148
edited Nov 22 '18 at 4:19
Cœur
18.4k9109148
edited Nov 22 '18 at 4:19
Cœur
18.4k9109148
18.4k9109148
asked Nov 1 '14 at 10:35
SandeshSandesh
846
asked Nov 1 '14 at 10:35
SandeshSandesh
846
asked Nov 1 '14 at 10:35
SandeshSandesh
846
846
Can you edit your question and show what you want as output?
– Gordon Linoff
Nov 1 '14 at 12:39
add a comment |
Can you edit your question and show what you want as output?
– Gordon Linoff
Nov 1 '14 at 12:39
Can you edit your question and show what you want as output?
– Gordon Linoff
Nov 1 '14 at 12:39
Can you edit your question and show what you want as output?
– Gordon Linoff
Nov 1 '14 at 12:39
add a comment |
2 Answers
2
active
oldest
votes
You can do this by generating all the possible numbers and then finding the ones that don't match. The following should work in both Oracle and SQL Server:
with nums(id, seqnum, mx) as (
select t.id, t.mn as seqnum, t.mx
from test_number_min_max t
union all
select t.id, t.mn + 1, t.mx
from nums
where nums.seqnum < t.mx
)
select nums.id, nums.seqnum
from nums left outer join
test_number tn
on tn.id = nums.id and tn.seqnumber = nums.seqnumber
where tn.id is null;
If there are more than 100 values between the minimum and maximum, then you will need to set the maximum recursion. If performance is an issue, you might want another way of generating the numbers. Here is one possibility (that should also work in both databases):
with nums as (
select row_number() over (order by id) - 1 as n
from test_number
)
select tnmm.id, tnmm.mn + nums.n as seqnumber
from test_number_min_max tnmm join
nums
on tnmm.mn + nums.n <= tnmm.mx left join
test_number tn
on tn.id = tnmm.id and
tn.seqnumber = tnmm.mn + nums.n
where tn.id is null;
This assumes that there are enough rows in test_number to enumerate the largest range in test_number_min_max (a reasonable assumption, but it might not be true).
answered Nov 1 '14 at 12:44
Gordon LinoffGordon Linoff
778k35307410
Thanks Gordon Linoff :)
– Sandesh
Nov 3 '14 at 6:06
add a comment |
CREATE TABLE test_number(id INTEGER,SEQ INTEGER,text varchar) ;
INSERT INTO test_number VALUES
(1,1,'AA')
, (1,3,'CC') , (1,4,'DD') , (1,5,'EE') , (1,6,'FF') , (1,7,'GG')
, (1,8,'HH') , (1,10,'JJ')
, (2,1,'KK') , (2,2,'LL') , (2,3,'MM') , (2,4,'NN')
, (2,6,'PP') , (2,7,'QQ')
, (3,1,'TT')
, (3,4,'ZZ') , (3,5,'XX')
;
SELECT t1.id AS "Id"
, t1.seq+1 AS "Start"
, t2.seq-1 AS "Stop"
, t2.seq - t1.seq -1 AS "Gapsize"
FROM test_number t1
JOIN test_number t2 ON t2.id = t1.id AND t2.seq > t1.seq + 1
WHERE NOT EXISTS (
SELECT * FROM test_number nx
WHERE nx.id = t1.id
AND nx.seq > t1.seq
AND nx.seq < t2.seq
);
Result:
Id | Start | Stop | Gapsize
----+-------+------+---------
1 | 2 | 2 | 1
1 | 9 | 9 | 1
2 | 5 | 5 | 1
3 | 2 | 3 | 2
(4 rows)
answered Nov 1 '14 at 13:38
wildplasserwildplasser
31.3k54270
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
});
}
});
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%2f26688382%2ffinding-only-missing-sequence-number-with-id%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can do this by generating all the possible numbers and then finding the ones that don't match. The following should work in both Oracle and SQL Server:
with nums(id, seqnum, mx) as (
select t.id, t.mn as seqnum, t.mx
from test_number_min_max t
union all
select t.id, t.mn + 1, t.mx
from nums
where nums.seqnum < t.mx
)
select nums.id, nums.seqnum
from nums left outer join
test_number tn
on tn.id = nums.id and tn.seqnumber = nums.seqnumber
where tn.id is null;
If there are more than 100 values between the minimum and maximum, then you will need to set the maximum recursion. If performance is an issue, you might want another way of generating the numbers. Here is one possibility (that should also work in both databases):
with nums as (
select row_number() over (order by id) - 1 as n
from test_number
)
select tnmm.id, tnmm.mn + nums.n as seqnumber
from test_number_min_max tnmm join
nums
on tnmm.mn + nums.n <= tnmm.mx left join
test_number tn
on tn.id = tnmm.id and
tn.seqnumber = tnmm.mn + nums.n
where tn.id is null;
This assumes that there are enough rows in test_number to enumerate the largest range in test_number_min_max (a reasonable assumption, but it might not be true).
answered Nov 1 '14 at 12:44
Gordon LinoffGordon Linoff
778k35307410
Thanks Gordon Linoff :)
– Sandesh
Nov 3 '14 at 6:06
add a comment |
You can do this by generating all the possible numbers and then finding the ones that don't match. The following should work in both Oracle and SQL Server:
with nums(id, seqnum, mx) as (
select t.id, t.mn as seqnum, t.mx
from test_number_min_max t
union all
select t.id, t.mn + 1, t.mx
from nums
where nums.seqnum < t.mx
)
select nums.id, nums.seqnum
from nums left outer join
test_number tn
on tn.id = nums.id and tn.seqnumber = nums.seqnumber
where tn.id is null;
If there are more than 100 values between the minimum and maximum, then you will need to set the maximum recursion. If performance is an issue, you might want another way of generating the numbers. Here is one possibility (that should also work in both databases):
with nums as (
select row_number() over (order by id) - 1 as n
from test_number
)
select tnmm.id, tnmm.mn + nums.n as seqnumber
from test_number_min_max tnmm join
nums
on tnmm.mn + nums.n <= tnmm.mx left join
test_number tn
on tn.id = tnmm.id and
tn.seqnumber = tnmm.mn + nums.n
where tn.id is null;
This assumes that there are enough rows in test_number to enumerate the largest range in test_number_min_max (a reasonable assumption, but it might not be true).
answered Nov 1 '14 at 12:44
Gordon LinoffGordon Linoff
778k35307410
Thanks Gordon Linoff :)
– Sandesh
Nov 3 '14 at 6:06
add a comment |
You can do this by generating all the possible numbers and then finding the ones that don't match. The following should work in both Oracle and SQL Server:
with nums(id, seqnum, mx) as (
select t.id, t.mn as seqnum, t.mx
from test_number_min_max t
union all
select t.id, t.mn + 1, t.mx
from nums
where nums.seqnum < t.mx
)
select nums.id, nums.seqnum
from nums left outer join
test_number tn
on tn.id = nums.id and tn.seqnumber = nums.seqnumber
where tn.id is null;
If there are more than 100 values between the minimum and maximum, then you will need to set the maximum recursion. If performance is an issue, you might want another way of generating the numbers. Here is one possibility (that should also work in both databases):
with nums as (
select row_number() over (order by id) - 1 as n
from test_number
)
select tnmm.id, tnmm.mn + nums.n as seqnumber
from test_number_min_max tnmm join
nums
on tnmm.mn + nums.n <= tnmm.mx left join
test_number tn
on tn.id = tnmm.id and
tn.seqnumber = tnmm.mn + nums.n
where tn.id is null;
This assumes that there are enough rows in test_number to enumerate the largest range in test_number_min_max (a reasonable assumption, but it might not be true).
answered Nov 1 '14 at 12:44
Gordon LinoffGordon Linoff
778k35307410
You can do this by generating all the possible numbers and then finding the ones that don't match. The following should work in both Oracle and SQL Server:
with nums(id, seqnum, mx) as (
select t.id, t.mn as seqnum, t.mx
from test_number_min_max t
union all
select t.id, t.mn + 1, t.mx
from nums
where nums.seqnum < t.mx
)
select nums.id, nums.seqnum
from nums left outer join
test_number tn
on tn.id = nums.id and tn.seqnumber = nums.seqnumber
where tn.id is null;
If there are more than 100 values between the minimum and maximum, then you will need to set the maximum recursion. If performance is an issue, you might want another way of generating the numbers. Here is one possibility (that should also work in both databases):
with nums as (
select row_number() over (order by id) - 1 as n
from test_number
)
select tnmm.id, tnmm.mn + nums.n as seqnumber
from test_number_min_max tnmm join
nums
on tnmm.mn + nums.n <= tnmm.mx left join
test_number tn
on tn.id = tnmm.id and
tn.seqnumber = tnmm.mn + nums.n
where tn.id is null;
This assumes that there are enough rows in test_number to enumerate the largest range in test_number_min_max (a reasonable assumption, but it might not be true).
answered Nov 1 '14 at 12:44
Gordon LinoffGordon Linoff
778k35307410
answered Nov 1 '14 at 12:44
Gordon LinoffGordon Linoff
778k35307410
answered Nov 1 '14 at 12:44
Gordon LinoffGordon Linoff
778k35307410
answered Nov 1 '14 at 12:44
Gordon LinoffGordon Linoff
778k35307410
778k35307410
Thanks Gordon Linoff :)
– Sandesh
Nov 3 '14 at 6:06
add a comment |
Thanks Gordon Linoff :)
– Sandesh
Nov 3 '14 at 6:06
Thanks Gordon Linoff :)
– Sandesh
Nov 3 '14 at 6:06
Thanks Gordon Linoff :)
– Sandesh
Nov 3 '14 at 6:06
add a comment |
CREATE TABLE test_number(id INTEGER,SEQ INTEGER,text varchar) ;
INSERT INTO test_number VALUES
(1,1,'AA')
, (1,3,'CC') , (1,4,'DD') , (1,5,'EE') , (1,6,'FF') , (1,7,'GG')
, (1,8,'HH') , (1,10,'JJ')
, (2,1,'KK') , (2,2,'LL') , (2,3,'MM') , (2,4,'NN')
, (2,6,'PP') , (2,7,'QQ')
, (3,1,'TT')
, (3,4,'ZZ') , (3,5,'XX')
;
SELECT t1.id AS "Id"
, t1.seq+1 AS "Start"
, t2.seq-1 AS "Stop"
, t2.seq - t1.seq -1 AS "Gapsize"
FROM test_number t1
JOIN test_number t2 ON t2.id = t1.id AND t2.seq > t1.seq + 1
WHERE NOT EXISTS (
SELECT * FROM test_number nx
WHERE nx.id = t1.id
AND nx.seq > t1.seq
AND nx.seq < t2.seq
);
Result:
Id | Start | Stop | Gapsize
----+-------+------+---------
1 | 2 | 2 | 1
1 | 9 | 9 | 1
2 | 5 | 5 | 1
3 | 2 | 3 | 2
(4 rows)
answered Nov 1 '14 at 13:38
wildplasserwildplasser
31.3k54270
add a comment |
CREATE TABLE test_number(id INTEGER,SEQ INTEGER,text varchar) ;
INSERT INTO test_number VALUES
(1,1,'AA')
, (1,3,'CC') , (1,4,'DD') , (1,5,'EE') , (1,6,'FF') , (1,7,'GG')
, (1,8,'HH') , (1,10,'JJ')
, (2,1,'KK') , (2,2,'LL') , (2,3,'MM') , (2,4,'NN')
, (2,6,'PP') , (2,7,'QQ')
, (3,1,'TT')
, (3,4,'ZZ') , (3,5,'XX')
;
SELECT t1.id AS "Id"
, t1.seq+1 AS "Start"
, t2.seq-1 AS "Stop"
, t2.seq - t1.seq -1 AS "Gapsize"
FROM test_number t1
JOIN test_number t2 ON t2.id = t1.id AND t2.seq > t1.seq + 1
WHERE NOT EXISTS (
SELECT * FROM test_number nx
WHERE nx.id = t1.id
AND nx.seq > t1.seq
AND nx.seq < t2.seq
);
Result:
Id | Start | Stop | Gapsize
----+-------+------+---------
1 | 2 | 2 | 1
1 | 9 | 9 | 1
2 | 5 | 5 | 1
3 | 2 | 3 | 2
(4 rows)
answered Nov 1 '14 at 13:38
wildplasserwildplasser
31.3k54270
add a comment |
CREATE TABLE test_number(id INTEGER,SEQ INTEGER,text varchar) ;
INSERT INTO test_number VALUES
(1,1,'AA')
, (1,3,'CC') , (1,4,'DD') , (1,5,'EE') , (1,6,'FF') , (1,7,'GG')
, (1,8,'HH') , (1,10,'JJ')
, (2,1,'KK') , (2,2,'LL') , (2,3,'MM') , (2,4,'NN')
, (2,6,'PP') , (2,7,'QQ')
, (3,1,'TT')
, (3,4,'ZZ') , (3,5,'XX')
;
SELECT t1.id AS "Id"
, t1.seq+1 AS "Start"
, t2.seq-1 AS "Stop"
, t2.seq - t1.seq -1 AS "Gapsize"
FROM test_number t1
JOIN test_number t2 ON t2.id = t1.id AND t2.seq > t1.seq + 1
WHERE NOT EXISTS (
SELECT * FROM test_number nx
WHERE nx.id = t1.id
AND nx.seq > t1.seq
AND nx.seq < t2.seq
);
Result:
Id | Start | Stop | Gapsize
----+-------+------+---------
1 | 2 | 2 | 1
1 | 9 | 9 | 1
2 | 5 | 5 | 1
3 | 2 | 3 | 2
(4 rows)
answered Nov 1 '14 at 13:38
wildplasserwildplasser
31.3k54270
CREATE TABLE test_number(id INTEGER,SEQ INTEGER,text varchar) ;
INSERT INTO test_number VALUES
(1,1,'AA')
, (1,3,'CC') , (1,4,'DD') , (1,5,'EE') , (1,6,'FF') , (1,7,'GG')
, (1,8,'HH') , (1,10,'JJ')
, (2,1,'KK') , (2,2,'LL') , (2,3,'MM') , (2,4,'NN')
, (2,6,'PP') , (2,7,'QQ')
, (3,1,'TT')
, (3,4,'ZZ') , (3,5,'XX')
;
SELECT t1.id AS "Id"
, t1.seq+1 AS "Start"
, t2.seq-1 AS "Stop"
, t2.seq - t1.seq -1 AS "Gapsize"
FROM test_number t1
JOIN test_number t2 ON t2.id = t1.id AND t2.seq > t1.seq + 1
WHERE NOT EXISTS (
SELECT * FROM test_number nx
WHERE nx.id = t1.id
AND nx.seq > t1.seq
AND nx.seq < t2.seq
);
Result:
Id | Start | Stop | Gapsize
----+-------+------+---------
1 | 2 | 2 | 1
1 | 9 | 9 | 1
2 | 5 | 5 | 1
3 | 2 | 3 | 2
(4 rows)
answered Nov 1 '14 at 13:38
wildplasserwildplasser
31.3k54270
answered Nov 1 '14 at 13:38
wildplasserwildplasser
31.3k54270
answered Nov 1 '14 at 13:38
wildplasserwildplasser
31.3k54270
answered Nov 1 '14 at 13:38
wildplasserwildplasser
31.3k54270
31.3k54270
add a comment |
add a comment |
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.
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%2f26688382%2ffinding-only-missing-sequence-number-with-id%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
Can you edit your question and show what you want as output?
– Gordon Linoff
Nov 1 '14 at 12:39