Re: Select between range, not in...

From: Richard Kuhler <noone_at_nowhere.com>
Date: Fri, 12 Dec 2003 20:33:33 GMT
Message-ID: <xCpCb.159$jK3.42@twister.socal.rr.com>

Pret Orian wrote:
> Hi there!
> I have a table with used IP Adresses, and I have a table with
> subnetworks (with IP range: start/end).
> I need to make a select statement which would return the first free IP
> in range.
> The catch is I'd need to find a solution that would use basic SQL
> query as I have no permission to write SPs or functions.
>
> So this would be the table structures:
>
> Subnet:
> sune_id, ipstart, ipend, ...
>
> IPs:
> ..., sune_id, ipadress
>
> note, ipstart, ipend and ipadress are plain integers (i.e. 4-Byte
> adresses downcalculated to integers, so you can refer to them
> acordingly).

Here are a couple ways to look for gaps in values ...

select min(id + 1)
from t
where not exists (

     select *
     from t next
     where next.id = t.id + 1)

/

select min(id + 1)
from (

         select id, lead(id) over (order by id) next_id
         from t
     ) v

where next_id is null or next_id != id + 1 /

--
Richard

Received on Fri Dec 12 2003 - 14:33:33 CST