Forum Discussion

John180's avatar
John180
Copper Contributor
Jan 19, 2024
Solved

Find the first free number in the SQL database

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
  • LainRobertson's avatar
    LainRobertson
    Jan 20, 2024

    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

     

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

     

    Cheers,

    Lain

2 Replies

Sort By
  • LainRobertson's avatar
    LainRobertson
    Silver Contributor
    Jan 20, 2024

    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

     

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

     

    Cheers,

    Lain

    • John180's avatar
      John180
      Copper Contributor
      Jan 20, 2024
      Hello,

      MEGACOOOLLLL - I still have a lot to learn.

      Thanks

Resources