Chalenge returning the first available number in a sequence

Hi all,

Ive been trying to figure this out and has proven to be quite difficult for me. Lets say i do a select on all client numbers from a clients table i would want the first available number returned.

Given the client table below my query would return 4 because its the lowest number availeble.

Thanks.

client table

clnum

1

2

3

6

7

Writing a single sql query for this task is challenging. Though I suggest that you could write a stored procedure that loops through a cursor pointing to clnum and check which number isn't present in the column:

psuedo code

open a cusor on clnum

loop i =1 to max int

loop thru the cursor and check if i is present ; if not then return i

end of loop

close the cursor

For help on using loops and cursors,seehttp://www.databasejournal.com/features/mssql/article.php/3111031

Hope this helps.

QI.

|||

Create a temp table or table variable, insert your clientid's and query from it. Something like this:

Declare @.ttable( Idint identity, clientidint)insert into @.tselect 1unionallselect 2unionallselect 3unionallselect 6unionallselect 7select top 1 IDfrom @.tWhere Idnot in (Select clientidfrom @.t)order by id

|||

This would not work if we already had 4 and 5 in the column as well. For example:

Declare @.ttable( Idintidentity, clientidint)

insertinto @.t

select 1unionall

select 2unionall

select 3unionall

select 4unionall

select 5unionall

select 6unionall

select 7

selecttop 1 ID

from @.tWhere Idnotin(Select clientidfrom @.t)

orderby id

The above code wouldn't return anything. Even though it should return 8, which is the lowest available number. Also, you want to be able to select from a column which may have an unknown number of rows so I don't think you can keep on doing unions like this.

QI.

|||

(1) If the ID returned is NULL, you can always query back with MAX(Clientid) and increment by 1. If you write a function to do this, it should be very simple.

(2) There is no need to manually write the INSERTs. That was just for a sample.

INSERT INTO @.T

SELECT Clientid

FROM Clients

would insert all the clients into the table.