Re: lowest free number

Michael Daehling <daehling_at_fhzinfo.fh-bielefeld.de> wrote:

>I want to get the lowest free number of a column. The column is a continuous
>number and the primary key of the table. I want to recycle the old numbers
>after delete
>I think a PL/SQL-function can do this - or is there a simple solution?

A subtil SELECT statment can do that :-) But I'm not sure that it will be terriby efficient... You will probably prefer using larger numbers: 10 digits are enough, they provide 10 billions possible primary keys !!!

try :

select min(PK+1) from campagne a
where not exists

	  (select PK
	   from campagne b 
	   where b.PK=a.PK+1)

PK is the primary key of the campagne table.

> Michael Daehling
Received on Mon Jun 24 1996 - 00:00:00 CEST