On Mon, Jul 13, 2009 at 07:40:48PM -0400, Wes Freeman scratched on the wall: > Yeah, sorry about that. In two statements: > > select max(number) from table where number < ? > select min(number) from table where number > ?
I'm pretty sure you don't want to do it this way. What this does is gather every row that meets the WHERE condition and then runs a max() or min() aggregation function across all of those rows. That means that even if the column "number" has an index on it, between these two statements you're going to end up scanning the whole table. You also have the problem that you can't return the rest of the row. The min() and max() functions will extract the right answer, but something like this will not: SELECT min(number), other_column, FROM table WHERE number > ? In that case, it is VERY likely that the value of "other_column" will not come from the same row as "min(number)". For example: sqlite> create table t ( i integer, s char ); sqlite> insert into t values (1, 'a'); sqlite> insert into t values (2, 'b'); sqlite> insert into t values (3, 'c'); sqlite> select min(i), s from t; 1|c sqlite> This is because the min() and max() aggregations imply a GROUP BY. Since none is given, the whole result is grouped. That works fine for min() and max() and gives you the right answer, but any other column you specify is simply going to return the value for the last row processed. That's why the above example returns 'c' for the second column. > >>> select * from table where number <= ? order by number desc limit 1; > >>> select * from table where number>= ? order by number asc limit 1; This is the best solution, especially if the "number" column has an index on it. In that case the correct row can be extracted directly from the next and results are nearly instant, no matter how large the table is. As others have pointed out, you do, of course, want to use < and >, and not <= and >=. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
