Problem with query

Hello everyone.
Does anybody have an idea how to optimize following?

I have a table - containing primary key (not identity) wich - on a new entry- should always be the lowest possible number (e.g. 1-234 and 236-400 are occupied - it should select 235, not 401).

Currently I'm doing it trough a dlookup loop searching for a free number (lowest possible)...

Is there any sql query (select top 1 or something like that) to speed this up?

Thanks for any replies!
Greetz - Marcselect min(key) from yourtable

ask yourself why you want to fill in the gaps

a primary key should have no meaning

a surrogate key (an assigned number, for instance) should not even be visible to users of the application

rudy
http://rudy.ca/|||Dear Rudy,
Select min(key) would give me back the lowest existing number - i need the lowest free number.
Primary Key was a bit wrong told - it has a identity besides this number, but i need this number to be unique, and given each time as low as possible (not taken)...

I'm using the system to make reservations in the local database - e.g. article ... gets reserved for a customer under reservation number 5, 1-4 and 6-10 are taken...|||doh!! (smacks self on head)

sorry, i answered too quickly, of course min(key) doesn't get the lowest available number

i could give you some sql, but it involves a left outer join with a temp table containing every integer smaller than max(key) -- it would be ugly and slow

as i said, you should re-think why you want the number to "fill in the gaps"

rudy|||Dear Rudy,
I need it to be the lowest possible number - else it would fill up my whole office :).
I've thought about this reservation system because you always have a low number (max) and so you can sort in the articles by reservation number - wich wouldn't exceed 1000 (except if really more than one thousand would be reserved)...
You can imagine it like this:
I have a wall full of articles with numbers, reserved for customers...
If I would have a ongoing number, the wall would have to get bigger and bigger (if I sort them in ascending by number)...

Do you think it would be faster if I do a SELECT * on a recordset object and loop trough than dlookup?|||consider this --create table reservations
(id integer primary key
, title varchar(50) not null
);
insert into reservations (id, title)
values (1, 'the first one');
insert into reservations (id, title)
values (937, 'the second one');
insert into reservations (id, title)
values (2, 'the third one');how big is your table? three rows

trust me, you do not have to re-use numbers to prevent your table from growing

the database does not reserve space for missing entries

;)|||Dear Robert,
It doesn't consider me if the table grows - the wall with the reservations would have to grow if i reserve by number...

e.g. pos 1 is number 1 - until pos 600 reserved... wall is full sorted by reservation number... now if I don't re-use the numbers wich go out (e.g. 50 gets sent)... my numbers will go to 1200 sometime... and i cannot store that reservation number in my office :)...|||let me explain otherwise...
i have a wall - with places for articles (reservations) - this wall is numbered from 1 to 1000 - so i need the reservations go from 1 to max 1000 - i need to reuse those numbers, elsewhere i would have to make the wall bigger ...
so what I do currently, is loop trough the table with a counter looking for a free position...|||create a new table for your reservation numbers, 1 through 600 or whatever (you can even use auto_number for this)

create table reservationnumbers
( resno integer not null
, reservationid integer null
)

reservationid will point to a reservation your existing table of reservations, the ones that can get deleted -- go ahead and assign them with an autonumber

when you want to assign a new reservation, use

select min(resno) from reservationnumbers
where reservationid is null

when you delete a reservation, make sure you set the corresponding reservationid in the the reservationnumbers table to null

rudy|||You could create a simple table called 'ALLVALUES' with only a numeric field (MYVALUE) containing all possible values (Ex. 1 - 5000).
Obviously this field must be the primary key.

then you can create a query extracting the lowest value in your table not matching with values in ALLVALUES.

Select min(ALLVALUES.MYVALUE) from ALLVALUES
LEFT OUTER JOIN YOURTABLE on (
ALLVALUES.MYVALUE = YOURTABLE.Key )
WHERE YOURTABLE.Key IS NULL

I think this will work

Bye
movendra@.yahoo.com|||That solution didn't get in my mind at all - thanks guys! I'll try it this way...

thanks for the help!|||Let us say your table is called XXX and the column that you are seek to find the lowest unused value is COL_ID

select min(x1.COL_ID + 1)
from XXX x1 (nolock)
where not exists
(
select *
from XXX x2 (nolock)
where x2.COL_ID = (x1.COL_ID + 1)
)|||That solution didn't get in my mind at all - thanks guys! I'll try it this way...

thanks for the help!|||May this will be more understandable for you...

create table test(id int, name varchar(10))

insert test values(1,'1')
insert test values(3,'3')
insert test values(4,'4')
insert test values(5,'5')

select min(id+1) newid from test
where (id+1) not in (select id from test)

newid
----
2|||hey guys (snail and achorozy), that's pretty slick

unfortunately it doesn't find the gap at the front of the table, but i suppose that's a minor quibble, eh

:cool: