Get the first availble number from a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Get the first availble number from a table

Author

Topic

Jilani_sak
Starting Member

1 Post

Posted - 2008-08-12 : 06:42:49

I have an table with column "Id" and datatype is INT. I have inserted the 5 rows. Now Id column has values from 1-5.

Then I have deleted the 3rd and 5th row from table.

I need a function to get the first available Id so that when I insert new row, new row will take first available id
(in above ex: i need id to be 3).

If none of rows are not deleted then the first available id be 6.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 06:52:19

Why is filling the gaps important to you?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 06:58:30

[code]DECLARE @Sample TABLE (i INT)

INSERT @Sample
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 4 UNION ALL
SELECT 5

SELECT MIN(s1.i) + 1 AS [NewID]
FROM @Sample AS s1
LEFT JOIN @Sample AS s2 ON s2.i = s1.i + 1
WHERE s2.i IS NULL

INSERT @Sample
SELECT 3

SELECT MIN(s1.i) + 1 AS [NewID]
FROM @Sample AS s1
LEFT JOIN @Sample AS s2 ON s2.i = s1.i + 1
WHERE s2.i IS NULL[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources