SQL to find lowest available key

[TerryPercival]

I have a web page that forces a user to enter the primary key (just a number) and I want to change it to find the lowest available key in the table.

 

[bperry]

Hi there:
(1) What database are you using? (the proposed solutions might vary depending on your physical situation).

(2) It's not quite clear just what it is you are looking for. i.e. Is it the SQL statement needed to find the lowest key? (And if so, a bit more commentary re: the table definition and sample data would help us narrow down how to help you.)

rgrds etc
bp

 

[carp]

SELECT min(key_column_name) FROM my_table;

 

[carp]

OK, it's late and I'm typing faster than I'm thinking!
Assuming you are incrementing by 1, then you could try something like:

SELECT max(key_column_name)+1 FROM my_table;

 

[pipk]

just out of interest...

what if a record was deleted (in a cascaded delete) and you wanted to use the key value of the record just deleted i.e. the value needed is currently less than the max key value returned.

 

[TerryPercival]

SQL Server 2000. The key is varchar but only numbers are allowed. The key may contain 1,3 5,10,50. There are gaps because the key is entered by the user. Dumb, but I didn't write the thing.