next available number in a field

[blar9]

Field1
1
2
4
5
6

Is there a way for me to return the first available number ie 3?

 

[cfStarlight]

Try one of these two. Adapted from

http://groups.google.com/group/micr...read/thread/8cfa785a5b9fffb3/d459575702bc2cfb

SELECT TOP 1 T1.Field1+1 AS FirstAvailableNumber
FROM   @TheTable AS T1 JOIN @TheTable AS T2
       ON T2.Field1 > T1.Field1
GROUP BY T1.Field1
HAVING 	MIN(T2.Field1)-T1.Field1 > 1

SELECT  TOP 1 T1.Field1+1 FirstAvailableNumber
FROM    @TheTable AS T1 LEFT JOIN @TheTable AS T2
        ON T2.Field1 = T1.Field1+1
WHERE	T2.Field1 IS NULL

 

[rac2]

You mean, is there a SQL Server function, stored procedure, or system variable which gives that value, right?

I dont know but how about this

SELECT MIN(a.Field1) + 1
FROM MyTable a
LEFT JOIN MyTable b ON b.Field1 = a.Field1 + 1
WHERE b.Field1 IS NULL

 

[ptheriault]

blar9,
Do you mind if I ask why you are filling in missing values? Is this field the PK of the table? As a general best practice rule you should re-use pk values.

- Paul

- If at first you don't succeed, find out if the loser gets anything.