Click here to Skip to main content
15,886,851 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
like

1
2
3
4
15
16
17


i want to retrieve 6,7,8,9,10,11,12,13,14

how i can find with value is missing
Posted
Comments
Sandeep Mewara 29-Mar-13 5:32am    
What do you mean 'blank' identity value?

Try this:
SQL
DECLARE @tmp TABLE (ID INT)

INSERT INTO @tmp (ID)
VALUES(1)
INSERT INTO @tmp (ID)
VALUES(2)
INSERT INTO @tmp (ID)
VALUES(3)
INSERT INTO @tmp (ID)
VALUES(4)
INSERT INTO @tmp (ID)
VALUES(15)
INSERT INTO @tmp (ID)
VALUES(16)
INSERT INTO @tmp (ID)
VALUES(17)

DECLARE @mId INT

SELECT @mId = MAX(ID)
FROM @tmp

;WITH NewIds AS
(
	SELECT 1 AS nID 
	UNION ALL
	SELECT nID + 1 
	FROM NewIds
	WHERE nID<@mId
)
SELECT ni.nID
FROM NewIds AS ni 
WHERE ni.nID NOT IN (SELECT ID AS nID FROM @tmp)


In above code CTE[^] were used.

result:
nID
5
6
7
8
9
10
11
12
13
14


[EDIT]
By The Way... Bad table design! Please, alter your table, but first read it: IDENITY[^]

Example table design:
SQL
CREATE TABLE MyTable (ID INT INDENTITY(1,1), Column2 VARCHAR(30), Column3 INT)

[/EDIT]
 
Share this answer
 
v2
I think you need a numbers table. I found this with a search: numbers table[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900