Re: SQL question

Julie Jumes (jumes@bnr.ca) wrote: : I am looking for the smallest value in a table of numeric values for which : the next number is not contained in that table for any record. : So if we have records with num_val values of : 1 2 3 4 5 : 5 should be selected. For values : 1 2 3 5 6 8 : 3 should be selected. I wrote the following query which shows : (min) : 1 row retrieved : but no value is displayed under min. : select min(num_val) from test_table : where num_val>0 and not exists : (select num_val +1 from test_table) : Removing the not causes the query to return 1 as expected. : Should this work? : Please post responses to this group. I don't have an inter-net mail account. : Thanks in advance, : Julie C. Jumes There is a solution using a correlated subquery: select min(num_val) from test_table t1 where not exists (select num_val from test_table t2 where t2.num_val+1 = t1.num_val) Juergen Priemer (priemer@uni-muenster.de) University of Muenster Germany