Re: SQL Query Question; here is a solution!
Date: Fri, 11 May 2001 17:44:43 -0700
Message-ID: <9di0q8$jbg$1_at_stlnews.stl.ibm.com>
This is a recursive SQL query which works:
with k(f) as
(select distinct(1) as f from in1 union all select f+1 from k where f<100)
select * from k where f not in (select col1 from in1)
Your db must have recursive support and CTE(table expresssions) support to
handle such a query.
Thanks
Aakash
"Charcoal" <who_at_cares.com> wrote in message
news:tZPK6.141$V5.300_at_news1.rivrw1.nsw.optushome.com.au...
> Hi people,
>
> Assume I have a table which is just a list of numbers (only 1 column). I
> want to perform a query which shows every number under 100 which is NOT in
> the table. How do I do this?
>
> For example, in my MyTable there is the following data:
> 1, 4, 7, 32, 54 (there are 5 records in my table).
>
> Now how would I write a query to show all the numbers < 100 that are not
in
> this table? ie, it should show 2, 3, 5, 8, .... etc.
>
> Any ideas welcome?
>
> Charlie
>
>
>
>
Received on Sat May 12 2001 - 02:44:43 CEST