Re: SQL question

Julie I tried: select min(num_val) from test_table where num_val + 1 not in (select num_val from test_table); and, the value 3 comes up for me.... Bob Beaulieu bobb@netcom.com In article <2bo2ga$mu0@crchh327.bnr.ca> jumes@bnr.ca (Julie Jumes) writes: >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 -- _____ _____ ______ _____ _____ ___ ___ _____ __ | ___| |_ / |_ _|| _ \\ / _ \\ \\ \\/ / | ___| | | | ___| / /_ | | | / | _ | \\ / | ___| | |__ |_____| /_____| |__| |__|__\\ \\_/ \\_/ \\__/ |_____| |_____| ______ C O R P O R A T E T R A V E L S P E C I A L I S T S _______ * Open 7 Days eztravel@netcom.com * 64+ Int'l Toll-Free #'s * 24 Hr Voice Mail Tel: (408) 978-0808 * Preferred Rates * 24 Hr Paging Fax: (408) 978-7024 * Internet e-mail ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~