TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
C# Corner
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Find the Missing Sequential Number From Table
Ramesh Singh
Aug 22
2016
Code
626
0
0
facebook
twitter
linkedIn
Reddit
WhatsApp
Email
Bookmark
expand
DECLARE
@Minval
INT
,@Maxval
INT
DECLARE
@
Temp
TABLE
(ID
SMALLINT
,
Name
VARCHAR
(128))
INSERT
INTO
@
Temp
( ID ,
Name
)
SELECT
1,
'A'
UNION
ALL
SELECT
2,
'B'
UNION
ALL
SELECT
3,
'C'
UNION
ALL
SELECT
4,
'D'
UNION
ALL
SELECT
7,
'E'
UNION
ALL
SELECT
8,
'F'
UNION
ALL
SELECT
10,
'G'
UNION
ALL
SELECT
12,
'H'
UNION
ALL
SELECT
13,
'I'
UNION
ALL
SELECT
16,
'J'
UNION
ALL
SELECT
17,
'K'
UNION
ALL
SELECT
18,
'L'
UNION
ALL
SELECT
20,
'M'
UNION
ALL
SELECT
21,
'N'
UNION
ALL
SELECT
22,
'O'
UNION
ALL
SELECT
23,
'P'
UNION
ALL
SELECT
24,
'Q'
UNION
ALL
SELECT
25,
'R'
UNION
ALL
SELECT
27,
'S'
UNION
ALL
SELECT
28,
'T'
UNION
ALL
SELECT
29,
'U'
UNION
ALL
SELECT
30,
'V'
UNION
ALL
SELECT
31,
'W'
UNION
ALL
SELECT
33,
'X'
UNION
ALL
SELECT
34,
'Y'
UNION
ALL
SELECT
36,
'Z'
SELECT
@Minval=
MIN
(ID) ,@Maxval=
MAX
(ID)
FROM
@
Temp
;
WITH
GenNumber
AS
(
SELECT
@Minval
AS
num
UNION
ALL
SELECT
num+1
FROM
GenNumber
WHERE
num+1<=@Maxval
)
SELECT
num
FROM
GenNumber X
WHERE
NOT
EXISTS (
SELECT
*
FROM
@
Temp
Y
WHERE
X.num=Y.ID)
OPTION
(MAXRECURSION 10000)
Missing Sequential Number
Table
SQL Server