Re: SQL question

>From: jumes@bnr.ca (Julie Jumes) >Subject: SQL question >Date: 9 Nov 1993 12:30:02 GMT >X-Informix-List-Id: <news.4852> > >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) This is retrieving null. This is correct, because the not exists subquery only returns true if test_table is empty, so there is no row which satisfies the search criteria as specified (we can fix the specification later), and the MIN of an empty set is defined, in SQL, as NULL. >Removing the not causes the query to return 1 as expected. >Should this work? It does work, but not as required. Re-phrasing the query, you wish to select the minimum number X such that the value X + 1 is not found in the table. You can do this with the query: SELECT MIN(Num_val) FROM Test_table T1 WHERE NOT EXISTS (SELECT * FROM Test_table T2 WHERE T2.Num_val = T1.Num_val + 1); Note the use of the table aliases t1, t2 when there are two instances of the same table occurring in the statement. It helps make life clear to the reader, as well as the query optimiser. In your code, you could get away without using the aliases; in my version, they are mandatory. It doesn't matter what you list in the select-list of an EXISTS clause. This a correlated sub-query; it has to be run for each value in term. I'd suggest not doing this on a table of millions of rows. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> =========================================================================== For your info, this is the output from SET EXPLAIN ON. The table was an un-indexed temporary table. QUERY: ------ select min(num_val) from test_table t1 where not exists (select * from test_table t2 where t2.num_val = t1.num_val + 1); Estimated Cost: 13 Estimated # of Rows Returned: 1 1) johnl.t1: SEQUENTIAL SCAN Filters: NOT EXISTS <subquery> Subquery: --------- Estimated Cost: 2 Estimated # of Rows Returned: 1 1) johnl.t2: SEQUENTIAL SCAN Filters: johnl.t2.num_val = johnl.t1.num_val + 1