SOLVED

Find the first free number in the SQL database

Copper Contributor
Hello,
 
I have a long table with the fields:
  • PROJECTNUMBER = text field
  • PROJECTNAME = text field
  • PROJECTYEAR = text field

the projectnumbers are not strict added. I need an MS-SQL command that can find the first free project number.

Example: I have the project numbers: 1, 2, 3, 4, 7, 8, 9 and the script must return me the value "5".
 
Who can help me with an easy example script? It has to be done somehow via "top 20 row_number()...." or does anyone have another way?
 
Thanks
2 Replies
best response confirmed by John180 (Copper Contributor)
Solution

@John180 

 

Hi, John.

 

This isn't really what SQL Server is built for and it won't scale well, but it is possible.

 

Example staging script

CREATE TABLE [dbo].[table1]
(
	[number] [int] NOT NULL
	, [name] [varchar](64) NOT NULL
	, [year] [int] NOT NULL
)
GO

INSERT INTO
	[dbo].[table1]
VALUES
	(1, 'Project 1', 2024)
	, (2, 'Project 2', 2024)
	, (3, 'Project 3', 2024)
	, (4, 'Project 4', 2024)
	, (7, 'Project 7', 2024)
	, (8, 'Project 8', 2024)
	, (9, 'Project 9', 2024)
GO

 

Example query

SELECT
	TOP 1
	[row] AS [newId]
FROM
(
	SELECT
		ROW_NUMBER() OVER
			(
				ORDER BY
					[number]
			) AS [row]
		, [number]
	FROM
		[table1]
) AS [data]
WHERE
	[number] <> [row];

 

Example output

LainRobertson_0-1705741582866.png

 

The more accepted approach is to use an autoincrementing number, which avoids all the overhead of the above method.

 

Cheers,

Lain

Hello,

MEGACOOOLLLL - I still have a lot to learn.

Thanks
1 best response

Accepted Solutions
best response confirmed by John180 (Copper Contributor)
Solution

@John180 

 

Hi, John.

 

This isn't really what SQL Server is built for and it won't scale well, but it is possible.

 

Example staging script

CREATE TABLE [dbo].[table1]
(
	[number] [int] NOT NULL
	, [name] [varchar](64) NOT NULL
	, [year] [int] NOT NULL
)
GO

INSERT INTO
	[dbo].[table1]
VALUES
	(1, 'Project 1', 2024)
	, (2, 'Project 2', 2024)
	, (3, 'Project 3', 2024)
	, (4, 'Project 4', 2024)
	, (7, 'Project 7', 2024)
	, (8, 'Project 8', 2024)
	, (9, 'Project 9', 2024)
GO

 

Example query

SELECT
	TOP 1
	[row] AS [newId]
FROM
(
	SELECT
		ROW_NUMBER() OVER
			(
				ORDER BY
					[number]
			) AS [row]
		, [number]
	FROM
		[table1]
) AS [data]
WHERE
	[number] <> [row];

 

Example output

LainRobertson_0-1705741582866.png

 

The more accepted approach is to use an autoincrementing number, which avoids all the overhead of the above method.

 

Cheers,

Lain

View solution in original post