Results 1 to 15 of 15
  1. #1
    Join Date
    Jul 2002
    Posts
    87

    Unanswered: 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 - Marc

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select 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/

  3. #3
    Join Date
    Jul 2002
    Posts
    87
    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...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  5. #5
    Join Date
    Jul 2002
    Posts
    87
    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?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    consider this --
    Code:
    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


  7. #7
    Join Date
    Jul 2002
    Posts
    87
    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 ....

  8. #8
    Join Date
    Jul 2002
    Posts
    87
    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...

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  10. #10
    Join Date
    Jul 2002
    Posts
    5
    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

  11. #11
    Join Date
    Jul 2002
    Posts
    87
    That solution didn't get in my mind at all - thanks guys! I'll try it this way...

    thanks for the help!

  12. #12
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Let us say your table is called XXX and the column that you are seek to find the lowest unused value is COL_ID

    Code:
    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)
        )
    MCDBA

  13. #13
    Join Date
    Jul 2002
    Posts
    87
    That solution didn't get in my mind at all - thanks guys! I'll try it this way...

    thanks for the help!

  14. #14
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    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

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •