List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:April 30 1999 6:06pm
Subject:Re: Auto-Numbering
View as plain text  
Dave Crawford wrote:
> 
> In regards to a auto_increment 'ID' column, is there a way for the Mysql
> server to assign the LOWEST available 'record' number (if one is
> available) instead of using the highest existing record + 1? For example
> if I have 20 records and I delete row 11, when I insert the next record
> assign it number 11 instead of 21. Is there an easier way to accomplish
> this other than polling a range of record numbers and seeing which rows
> come back NULL? Thanks.
> 
> -Dave

Hi Dave

You can get the lowest available ID with:
SELECT 
        MIN(a.id+1) AS lowest 
FROM 
        table a 
        LEFT JOIN table b 
        ON a.id+1=b.id 
WHERE 
        b.id IS NULL;


Tschau
Christian

Thread
Auto-NumberingDave Crawford30 Apr
  • Re: Auto-NumberingFred Read30 Apr
  • Re: Auto-NumberingChristian Mack30 Apr