SQL Query finding next unused number in a property.

[Habalabam]

This is a bit of an SQL nut (at least to me).

I want to find the SQL statement that gets the next unused number within that category.

Simplified Table:

Table name: MyUnit.
Attributes:
-UnitNo
-CategoryNo


Let's assume the statement
SELECT UnitNo FROM MyUnit WHERE (CategoryNo=@Category)
returns the set {1,2,3,4,5,7,8,10,11,12} if Category=3.

I want to find the SQL statement that have two parameters: "Category" and "MinimumNo"

Examples:

"Category" = 3
"MinimumNo" = 2 should return 6

"Category" = 3
"MinimumNo" = 5 should return 6

"Category" = 3
"MinimumNo" = 7 should return 9

"Category" = 3
"MinimumNo" = 12 should return 13

"Category" = 3
"MinimumNo" = 17 should return 17

I hope I don't have to create a separate table containing all valid Unit numbers...

 

[JarlH]

SELECT MIN(UnitNo+1) FROM MyUnit
WHERE UnitNo+1 >= 2
AND CategoryNo = 3
AND UnitNo+1 NOT IN
(SELECT UnitNo FROM MyUnit WHERE CategoryNo = 3)

 

[Habalabam]

Thank you very much! So simple...

 

[Habalabam]

Oh, there is one not-so-small problem: I would like the SQL statement to handle this:

"Category" = 3
"MinimumNo" = 4 should return 4

If the only record in the table MyUnit
-UnitNo = 12
-CategoryNo = 3

Maybe I can check Table MyUnit for a record with UnitNo that equals @MinimumNo-1, and if it does not exist, create it(only within the scope of the statement using a merge/INNER JOIN, not in the table itself), and then use the SQL statement....?