Re: SQL question

Julie 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. Because it is null. min() always returns something - even if its null. > > > 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. Pardon me while I think through this. (I'm deadly serious I don't understand it). The subquery is returning: (based on 1,2,4,5) (we want it to return '2') 2 3 5 6 ( 2 exists here and is the min, but.....) (based on 1,2,3,4,6,7) (we want a '4') 2 3 4 5 7 8 ( 4 exists here and is not the min) if we did: (select num_val -1 from test_table) ^ we'd get: 0 1 3 4 and 0 1 2 3 5 6 In which case the 2 and the 4 are missing which is what we want. But: select min(num_val) from test_tab where not exists (select num_val - 1 from test_table ) still returns null. in fact: select min(num_val) from test_tab where exists (select num_val + 7 from test_table ) returns 1. ^^^ This is because the exists condition is doing a boolean. It can always select rows from test_table. What you are asking is: Give me the minimum value from test_table (1) so far. Where it's > 0 (still 1). and not TRUE (i.e. FALSE) (False is never true - therefore no value returned => null) or (without the not) and TRUE (1 is still ok). When what you want is: Give me the minimum value from test_table where it's > 0 AND FALSE WHEN NEXT VALUE DOES NOT EXIST So we need to make that FALSE into a sometimes TRUE. We need to make it sometimes NOT find rows in the subquery. So: select min(num_val) from test_tab where num_val > 0 and not exists (select num_val - 1 from test_table where - some condition that joins this select to what is in the main select num_val=num_val? - uh oh that ain't allowed) So we need to build another table or something that we can subquery against. select num_val - 1 vcol from test_table into temp x; # I won't explain vcol select min(num_val) from test_table where num_val > 0 and not exists (select vcol from x where num_val = vcol); It works! Thank you for making me understand that. Ever since David Stockel passed me the exclusive select trick I've been trying to understand it. I think I finally got it. (P.S. thanks again Dave!) - oh by the way - I hope that solves your problem. I'm sorry about the temp table. j. _____________________________________________________________________________ Jack Parker | Hewlett Packard, BSMC Boise, Idaho, USA| Your .sig has expired, please enter jparker@hpbs2561.boi.hp.com | a new one. (208) 396-5388 (W) (208) 384-1623 (H) | _____________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. _____________________________________________________________________________