February 22, 2008 at 7:08 pm
I have a table with column containing numbers for example
1.. to 1000 all numbers taken
1001
1002
1003
1004
2000
How can I make select query that will show me as result 1004.I need the smalest available
number as result of my query.
February 22, 2008 at 8:22 pm
Here is one option:
select MIN(LowestNumber.PK)
FROM MyTable LowestNumber
LEFT JOIN MyTable NextNumber
ON NextNumber.pk = LowestNumber.pk + 1
WHERE NextNumber.PK IS NULL
I think there are probably several ways to do it, and I'm sure someone will post something more efficient. (try a Google for mssql find missing in sequence and you'll see a few different answers)
I'm guessing that you are trying to fill in holes a primary key field? Running some kind of query (the one I suggested, or any other one) to find the value to insert could create a little bit of a performance issue. It might be easier to start at zero and go negative with the IDs if you are using an int, or change to a larger field (bigint?), but then your storage and join memory needs change too... Something to consider though.
Hope this helps,
Chad
February 22, 2008 at 8:55 pm
Awesome.This is exactly what I needed. Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply