Thanks Atrix, I think I would tackle your problem by using a while loop which starts at the min(idnum) and goes up to the current max(idnum) and creates a (temp) table where it doesn't find a match. Even with a large data set this would be very fast with a temp table.
If you really want to re-use the currently unused numbers then instead of using an autonum column you would need to take the idnums from the temp table first then add/redefine the autonum definitions when the unused numbers are all used. It's not as hard as it might sound. If it's really a problem that you never have an unused number then create a control table that contains all the possible numbers and a flag to indicate whether it's used or not and take your next number(s) from the control table. Alternatively, put the unused numbers and the flag in the data table if you know that you have very few holes and mostly add new numbers at the end - then use the flagged number before adding new ones. This subject always causes quite heated debate on this list with half saying not only that it doesn't matter but also that it mustn't matter and the other half liking things tidy for some good reason. Me? I'm a tidy person - where possible. Regards, Alastair. ----- Original Message ----- From: "Atrix Wolfe" <[EMAIL PROTECTED]> To: "RBASE-L Mailing List" <[EMAIL PROTECTED]> Sent: Monday, September 08, 2003 11:11 PM Subject: [RBASE-L] - Re: Finding unused id codes > Hey Alastair, > > We have kinda been in need of something like this for a while too...we have > a tranmaster table that stores transaction ids and right now we do a select > max(tranid)+1 from tranmaster to get the next tranid but as transactions are > deleted and such, it leaves alot of holes in the id sequence. > > It has about 10,000 rows in the table, but the max id is up near 45,000! > > One way we thought about aproaching this problem was to make a utility that > would renumber all the tranids in tranmaster as well as all the other tables > that use the tranid. > > That would make it so our 10,000th row would also be tranid 10,000 instead > of 45,000 so wed be back on track. > > Im not sure how you get your ids or what they represent but if you do it > like us and just get the max(id)+1 to get the next, this might be of > use...im still keepin my eye out for a better solution too though so im glad > you brought this topic up! > > ----- Original Message ----- > From: "Alastair Burr" <[EMAIL PROTECTED]> > To: "RBASE-L Mailing List" <[EMAIL PROTECTED]> > Sent: Monday, September 08, 2003 3:02 PM > Subject: [RBASE-L] - Finding unused id codes > > > > Hello everyone, > > > > I'm trying to find what codes from a look-up table are not currently in > use: > > > > I have a data table that has, amongst other columns, a text column with a > > (variable) number of 2 character codes separated by a comma, viz: > > aa,ab,ac,ba,bb,bc... > > > > The look-up table has code column with a row for each code and a column > with > > a description. > > > > I can find which codes are in use easily enough and I thought that the > > missing ones could be found by "reversing" the logic but it doesn't work: > > > > Briefly, SELECT... WHERE ... CONTAINS tell me the ones in use but > SELECT... > > WHERE ... NOT CONTAINS gives me everything. > > > > I can think of a way to do it using a temp table but I wondered if there > was > > an SQL way to find the unused items? Anybody got any ideas? > > > > Thanks in advance, > > Regards, > > Alastair. > > > > ---------------------------------- > > A D B Burr, > > St. Albans, UK. > > ---------------------------------- > > [EMAIL PROTECTED] > > ---------------------------------- > > >
