Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!newsfeed.telusplanet.net!newsfeed.telus.net!snoopy.risq.qc.ca!headwall.stanford.edu!newshub.sdsu.edu!elnk-nf2-pas!elnk-pas-nf1!newsfeed.earthlink.net!cyclone.socal.rr.com!cyclone2.kc.rr.com!news2.kc.rr.com!twister.socal.rr.com.POSTED!53ab2750!not-for-mail
From: Richard Kuhler <noone@nowhere.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: Select between range, not in...
Lines: 42
References: <9849ae9d.0312120948.36f1c223@posting.google.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax)
X-Accept-Language: en-us, en
In-Reply-To: <9849ae9d.0312120948.36f1c223@posting.google.com>
Message-ID: <xCpCb.159$jK3.42@twister.socal.rr.com>
Date: Fri, 12 Dec 2003 20:33:33 GMT
NNTP-Posting-Host: 204.210.41.27
X-Complaints-To: abuse@rr.com
X-Trace: twister.socal.rr.com 1071261213 204.210.41.27 (Fri, 12 Dec 2003 12:33:33 PST)
NNTP-Posting-Date: Fri, 12 Dec 2003 12:33:33 PST
Organization: RoadRunner - West
Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:250016

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