From: Christian Mack
Date: April 30 1999 6:06pm
Subject: Re: Auto-Numbering
List-Archive: http://lists.mysql.com/mysql/2708
Message-Id: <3729F13C.39AB6350@compal.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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