Re: SQL question

From: Shujda <shujda_at_prodigy.net>
Date: Sat, 26 Dec 1998 21:24:01 -0500
Message-ID: <36859A40.594BA396@prodigy.net>

This is the select statement that returns the FIRST gap or 'free' number:
select min(gap)
from
(
-- this select returns next 'free' number for every gap, plus max(NR) + 1
select NR+1 gap
from TABLE
where NR not in (select NR-1 from TABLE) union
-- this select returns 1 if minimum NR is greater then 1, else it returns nex sequence
-- number that will be eliminated by union (union returns only distinct rows)
select decode(

              min(NR)-1
             ,0,max(NR)+1

    ,1
    ) gap
from TABLE
) gaps

Hope, I didn't miss something. Statement is tested on the single-column table
with the following values: 2,3,6,8,9,10. The statement in From clause returns 1,4,7,11. I'll be glad if somebody can beat it.
Regards

Burkhard Schultheis wrote:

> Hallo,
>
> if I have the following table:
>
> Nr Text
> -- ----
> 1 'Text1'
> 2 'Text2'
> 4 'Text3'
>
> I want to search for the first 'free' Nr, which should be 3 in this
> case. How to do it with one select?
> Thank you in advance!
>
> Burkhard Schultheis
> Tele Data
Received on Sat Dec 26 1998 - 20:24:01 CST