Re: [sqlite] range enclosing a number
On Tue, Jul 14, 2009 at 07:32:25AM -0400, Pavel Ivanov scratched on the wall: > Not to continue argument with Jay but just to express my opinion in > comparison: > > > The ORDER/LIMIT approach is much more resilient to changes, however, > > and should more or less behave the same no matter what you do to the > > rest of the query. > > Seriously, I don't believe this. There's no way to change the query so > that min/max will scan all table and order/limit will take just value > from index. They both will take the same approach in all cases (except > of course old SQLite which I fortunately didn't work with :) ). You clearly have experience with more robust databases, where I would expect this statement to be true. SQLite has a long history of keeping the engine as simple and small as possible, however. Part of that was a strong use of standard user-defined functions for the built-ins, like min/max/count. For years stuff like min/max used the same API as user-defined functions, and as such couldn't be touched by the optimizer. Even now, with the integration, it isn't that strong. Perhaps in other products, but not here. In specific, here's the comment in the code of the optimizer that attempts to short-cut a min/max: /* ** Analyze the SELECT statement passed as an argument to see if it ** is a min() or max() query. Return WHERE_ORDERBY_MIN or WHERE_ORDERBY_MAX if ** it is, or 0 otherwise. At present, a query is considered to be ** a min()/max() query if: ** ** 1. There is a single object in the FROM clause. ** ** 2. There is a single expression in the result set, and it is ** either min(x) or max(x), where x is a column reference. */ So, a simple JOIN (perhaps used as a filtering semijoin) will kill the min/max approach, but still leave the use of the index available for something like the ORDER/LIMIT approach. I'm not actually sure what SQLite will do if you add a JOIN, so I'm not saying I know the ORDER/LIMIT is faster, but this seems to say that min/max will go to a scan. Also, as pointed out, if x becomes an expression or computed column (even one that only accesses the indexed column), rather than a direct table reference, all bets are off with the SQLite optimizer. Sure, it is possible to write an optimizer that can deal with these situations, but that's not what we're dealing with here. We'd be having a different conversation if we were talking about a large-scale data-center product. I'll also admit I'm not sure these situations are extremely likely, but they're still valid examples, and I like to program defensively. > > The ORDER/LIMIT approach is, arguable, less graceful, but > > it is also (IMHO) a lot easier to break down into logical blocks that > > a newbie can follow and understand both the design and intent-- even > > if the query is a bit strung out. > > This is where my opinion is exactly opposite: query with min/max is > more readable by newbie just getting introduced to the code - it > clearly states what requester is trying to do: "get minimum among > records with this condition". OK, I'll buy that for someone that is just trying to figure out what it does without thinking about it very hard, the min/max does "read" better. But for someone trying to figure out how it works, and maybe change it, I still disagree. Most people think of the SELECT clause as a column-wise operator... that's where you define the vertical "shape" of your result. If you want to pick out a specific row, they think WHERE. Now clearly GROUP BY mixes this up, and allows you to alter the row configuration using the SELECT heading, but my own experience teaching people SQL has shown me that the two most confusing concepts for an SQL newbee are JOINs and GROUP BYs (see OP's response), and this depends on a GROUP BY that isn't even there (although at least it is a simple one!). Further, from a code maintenance standpoint, I'd be seriously afraid someone look at this, think "Oh, its obvious this picks out the _row_ with the max value," and do something like add a second column to the query (like the OP is needing to do). Only problem is that's not how that query works. Even worse, as we've already talked about, if you do add a column the query still runs, still works, still returns a value-- it just happens to be the wrong value. Unless you've got a strong understanding of how GROUP BY works (see above note about learning SQL) someone is going to spend hours trying to debug that one. From a set-wise, higher-thinking approach I agree that min/max is cleaner and more concise, and has the advantage of also running fast IF you have a good optimizer. But I also consider it a "clever" piece of code. ORDER/LIMIT is bigger, and has more bits that you need to piece together in your mind to see the big picture, but the individual bits are, in my mind, more isolated and simpler
Re: [sqlite] range enclosing a number
Not to continue argument with Jay but just to express my opinion in comparison: > The ORDER/LIMIT approach is much more resilient to changes, however, > and should more or less behave the same no matter what you do to the > rest of the query. Seriously, I don't believe this. There's no way to change the query so that min/max will scan all table and order/limit will take just value from index. They both will take the same approach in all cases (except of course old SQLite which I fortunately didn't work with :) ). > The ORDER/LIMIT approach is, arguable, less graceful, but > it is also (IMHO) a lot easier to break down into logical blocks that > a newbie can follow and understand both the design and intent-- even > if the query is a bit strung out. This is where my opinion is exactly opposite: query with min/max is more readable by newbie just getting introduced to the code - it clearly states what requester is trying to do: "get minimum among records with this condition". Order/limit approach requires more thinking before you clearly understand what was the intention behind the query - I've struggled myself trying to understand which sign (< or >) should go with which order by (desc or asc). But... Here are opinions and they have already become an off-topic because the OP's case looks like more complicated and couldn't be solved by simple min/max. And in response to Bogdan's letter: > Adding the second column as in:select max(ticks),time from time_pair where > ticks <= ?; > seems to work, although I don't understand the GROUP BY comments. The essence of "GROUP BY comments" is that if you write query this way then what is returned in time column is implementation dependent (not all sql engines even support this syntax) and you better think that in this case value in time column is completely arbitrary and unrelated to actual data in the table. So if you want to return both columns you should use either order/limit approach or already mentioned "select * ... where ticks = (select max(ticks) ...)" approach. Pavel On Tue, Jul 14, 2009 at 12:35 AM, Jay A. Kreibich wrote: > On Mon, Jul 13, 2009 at 10:33:00PM -0400, Pavel Ivanov scratched on the wall: >> Jay, you're pretty much mistaken: >> >> > 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. >> >> All database engines optimize queries which ask for min/max on indexed >> column with condition including only <, > or = on this very column. >> And SQLite is among these too: > > > Not "all." This type of optimization is actually a fairly new > addition to SQLite (considering the product lifetime) and first > appeared in 3.5.5, which was released in early 2008. > > > And I'd still go with ORDER/LIMIT. Here's why: > > > For my tests I just used the default build under the current version > of Mac OS X, which is a bit old (3.4). Under that build, the > ORDER/LIMIT is clearly faster, as this is before the optimization > existed: > > (using the same test set you did) > > Full scan, 3.4: > - > real 0m5.99s > user 0m4.73s > sys 0m0.84s > > Using ORDER/LIMIT, 3.4: > - > real 0m0.00s > user 0m0.01s > sys 0m0.00s > > Using min/max, 3.4: > - > real 0m5.97s > user 0m2.94s > sys 0m0.38s > > In this case, it is clear that min/max are NOT integrated into the > optimizer, and requires half a table scan, just as I stated. > > I also have a build of the current 3.6.16 around, and in that case, > the numbers are better: > > Using ORDER/LIMIT, 3.6.16 > - > real 0m0.12s > user 0m0.01s > sys 0m0.03s > > Using min/max, 3.6.16 > - > real 0m0.04s > user 0m0.01s > sys 0m0.03s > > This clearly shows that the optimization does exist, and that for > this very basic case my assumptions were incorrect. > > With the current 3.6.16 build, using min/max seems a tad faster-- but > only in "real" time. In terms of user/sys times, the results shown > here (and you're own numbers, which were 0.043/0.001/0.005 and > 0.005/0.001/0.001) were pretty typical (i.e. very very close). > That might just be an I/O fluke. We're getting small enough that > to really say anything definite requires better profiling. So > there does appear to be a difference, but it is pretty small and > unclear where it is coming from. > > However, I'd point out that using ORDER/LIMIT under 3.4 is the > fastest of all. This isn't just a quirk of one run, either. > I ran these several times and the 3.4 ORDER/LIMIT was always fastest. > We're still playing with number to small to really trust, but it > seems that if the 3.6.16 ORDER/LIMIT was as fast as the one in 3.4, > i
Re: [sqlite] range enclosing a number
Thank you everybody for the incredible help. Maybe is better if I explain more the problem: I need to do piecewise linear interpolations between two very large sets of numbers. The interpolation is the reason I was using <= and>=). One set represents clock ticks (46 bits integers) and the other real times (floating point numbers). The table has two columns which are indexed and the elements are unique. The interpolation would have to be done either way: from ticks to times or from times to ticks. To perform the interpolation I need that select returns both columns. Adding the second column as in:select max(ticks),time from time_pair where ticks <= ?; seems to work, although I don't understand the GROUP BY comments. I have thought of edge conditions (there I need to do extrapolation) and planned either to retrieve the max and min beforehand and use them to compare with the current argument, or to do always something like: select * from time_pair where ticks <= ? order by ticks desc limit 2; i.e. try to retrieve two rows before (and similarly two after). Since the max and min cannot be reused because the database is closed between interpolation operations and might be altered, it's not clear which is the better approach, so I will have to test. The idea to try on both 3.4 and 3.6 is very good since since this is a mixed environment and ultimately it may run against 3.4. Thank you again for your time and efforts,Bogdan > Date: Mon, 13 Jul 2009 23:35:22 -0500 > From: j...@kreibi.ch > To: paiva...@gmail.com > CC: sqlite-users@sqlite.org > Subject: Re: [sqlite] range enclosing a number > > On Mon, Jul 13, 2009 at 10:33:00PM -0400, Pavel Ivanov scratched on the wall: >> Jay, you're pretty much mistaken: >> >>> 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. >> >> All database engines optimize queries which ask for min/max on indexed >> column with condition including only or = on this very column. >> And SQLite is among these too: > > > Not "all." This type of optimization is actually a fairly new > addition to SQLite (considering the product lifetime) and first > appeared in 3.5.5, which was released in early 2008. > > > And I'd still go with ORDER/LIMIT. Here's why: > > > For my tests I just used the default build under the current version > of Mac OS X, which is a bit old (3.4). Under that build, the > ORDER/LIMIT is clearly faster, as this is before the optimization > existed: > > (using the same test set you did) > > Full scan, 3.4: > - > real0m5.99s > user0m4.73s > sys 0m0.84s > > Using ORDER/LIMIT, 3.4: > - > real0m0.00s > user0m0.01s > sys 0m0.00s > > Using min/max, 3.4: > - > real0m5.97s > user0m2.94s > sys 0m0.38s > > In this case, it is clear that min/max are NOT integrated into the > optimizer, and requires half a table scan, just as I stated. > > I also have a build of the current 3.6.16 around, and in that case, > the numbers are better: > > Using ORDER/LIMIT, 3.6.16 > - > real0m0.12s > user0m0.01s > sys 0m0.03s > > Using min/max, 3.6.16 > - > real0m0.04s > user0m0.01s > sys 0m0.03s > > This clearly shows that the optimization does exist, and that for > this very basic case my assumptions were incorrect. > > With the current 3.6.16 build, using min/max seems a tad faster-- but > only in "real" time. In terms of user/sys times, the results shown > here (and you're own numbers, which were 0.043/0.001/0.005 and > 0.005/0.001/0.001) were pretty typical (i.e. very very close). > That might just be an I/O fluke. We're getting small enough that > to really say anything definite requires better profiling. So > there does appear to be a difference, but it is pretty small and > unclear where it is coming from. > > However, I'd point out that using ORDER/LIMIT under 3.4 is the > fastest of all. This isn't just a quirk of one run, either. > I ran these several times and the 3.4 ORDER/LIMIT was always fastest. > We're still playing with number to small to really trust, but it > seems that if the 3.6.16
Re: [sqlite] range enclosing a number
On Mon, Jul 13, 2009 at 10:33:00PM -0400, Pavel Ivanov scratched on the wall: > Jay, you're pretty much mistaken: > > > 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. > > All database engines optimize queries which ask for min/max on indexed > column with condition including only <, > or = on this very column. > And SQLite is among these too: Not "all." This type of optimization is actually a fairly new addition to SQLite (considering the product lifetime) and first appeared in 3.5.5, which was released in early 2008. And I'd still go with ORDER/LIMIT. Here's why: For my tests I just used the default build under the current version of Mac OS X, which is a bit old (3.4). Under that build, the ORDER/LIMIT is clearly faster, as this is before the optimization existed: (using the same test set you did) Full scan, 3.4: - real 0m5.99s user 0m4.73s sys 0m0.84s Using ORDER/LIMIT, 3.4: - real 0m0.00s user 0m0.01s sys 0m0.00s Using min/max, 3.4: - real 0m5.97s user 0m2.94s sys 0m0.38s In this case, it is clear that min/max are NOT integrated into the optimizer, and requires half a table scan, just as I stated. I also have a build of the current 3.6.16 around, and in that case, the numbers are better: Using ORDER/LIMIT, 3.6.16 - real 0m0.12s user 0m0.01s sys 0m0.03s Using min/max, 3.6.16 - real 0m0.04s user 0m0.01s sys 0m0.03s This clearly shows that the optimization does exist, and that for this very basic case my assumptions were incorrect. With the current 3.6.16 build, using min/max seems a tad faster-- but only in "real" time. In terms of user/sys times, the results shown here (and you're own numbers, which were 0.043/0.001/0.005 and 0.005/0.001/0.001) were pretty typical (i.e. very very close). That might just be an I/O fluke. We're getting small enough that to really say anything definite requires better profiling. So there does appear to be a difference, but it is pretty small and unclear where it is coming from. However, I'd point out that using ORDER/LIMIT under 3.4 is the fastest of all. This isn't just a quirk of one run, either. I ran these several times and the 3.4 ORDER/LIMIT was always fastest. We're still playing with number to small to really trust, but it seems that if the 3.6.16 ORDER/LIMIT was as fast as the one in 3.4, it would likely be the best choice of all. So you've sold me that the current version of SQLite clearly does have the min/max optimization and doesn't require a table scan. It also appears to be slightly faster, but not by a big enough gap to clearly consider it a better choice on that alone. Personally, I'd still go with ORDER/LIMIT. With the current version of SQLite the runtimes of both approaches are extremely similar, but the min/max approach depends on the query optimizer being able to take the min/max notation and basically turn it into an internal ORDER BY (look at the code). There are a lot of limits on when the optimizer can do this. If the real-world query is a bit more complex than this example, or if (down the road) the query conditions get changed, or if the query gets more complex in just about anyway, the optimization is going to break with the min/max approach and you'll be stuck with a table scan-- something that I think we can all agree is MUCH slower. If that comes up due to a change in the SQL six months down the road, you're going to be spending a lot of time wondering why things just got so slow. The ORDER/LIMIT approach is much more resilient to changes, however, and should more or less behave the same no matter what you do to the rest of the query. The ORDER/LIMIT approach is also the clear winner if there is any possible chance at all you'll be running on older code. "Older," in this case, is code that was released less than two years ago. In many production environments, that's not all that long. There is also this... > But of course your other point is true - if you want some other data > from table along with min/max value, you need to make additional > select in case of using min/max. ... which also negates any possible advantage of using the min/max method. The ORDER/LIMIT approach lets you pull out all the data you might need or want in a single query. For the simple case on newer code, both approaches give similar returns. But the min/max approach will default to a very expensive behavior if it is changed in just about any way, while the ORDER/LIMI
Re: [sqlite] range enclosing a number
On 14/07/2009 11:44 AM, Jay A. Kreibich wrote: > 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. Let's assume right from the start that there's going to be an index on the subject column. too_slow * 2 == too_slow in boss arithmetic :-) Are you sure? I'm no expert on decoding the EXPLAIN output but the two look essentially the same to me: set up a cursor on the index, do a SeekLt(the_input_parameter) then test the limit in the first case, do exactly ONE AggStep operation in the other case > > You also have the problem that you can't return the rest of the row. (1) RowS plural. The limit 1 is arbitrary; there may be more than one row with such a value of number. (2) I would have thought it possible to return the rest of the rows using something like this: select * from table t1 where t1.number = (select max(t2.number) from table t2 where t2.number <= ?); with optional LIMIT if desired. My rules of thumb: (1) a sub-select like that can be used just about everywhere (2) whenever I see "limit 1" I get nervous and want to make absolutely sure that the query isn't going to generate a zillion rows and throw all but one away, or generate 5 and throw 4 away when somebody has presumed incorrectly that there would be only one row not 5. BTW, has the OP thought about the end conditions (no such lower value, no such higher value)? Cheers, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] range enclosing a number
Jay, you're pretty much mistaken: > 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. All database engines optimize queries which ask for min/max on indexed column with condition including only <, > or = on this very column. And SQLite is among these too: > rm test.db > ( echo "create table t (i integer); begin;"; for ((i = 0;i<1000;++i)); do > echo "insert into t values ($i);"; done; echo "end; create index t_i on t > (i);" ) | sqlite3 test.db > # First force the full table scan > time sqlite3 test.db "select count(*) from t where i * i < 500;" 223607 real0m1.610s user0m1.469s sys 0m0.125s > # now using index > time sqlite3 test.db "select * from t where i < 500 order by i desc limit > 1;" 499 real0m0.043s user0m0.001s sys 0m0.005s > time sqlite3 test.db "select max(i) from t where i < 500;" 499 real0m0.005s user0m0.001s sys 0m0.001s As you see using max() works better than "order by ... limit 1". But of course your other point is true - if you want some other data from table along with min/max value, you need to make additional select in case of using min/max. Pavel On Mon, Jul 13, 2009 at 9:44 PM, Jay A. Kreibich wrote: > 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] range enclosing a number
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
Re: [sqlite] range enclosing a number
Thank you, seems like a good solution. Best regards,Bogdan > From: freeman@gmail.com > Date: Mon, 13 Jul 2009 19:40:48 -0400 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] range enclosing a number > > Yeah, sorry about that. In two statements: > > select max(number) from table where number < ? > select min(number) from table where number> ? > > if you want to merge them into a single statement, you can do: > select (select max(number) from table where number < ?) > highest_smaller, (select min(number) from table where number> ?) > lowest_greater; > > On Mon, Jul 13, 2009 at 7:32 PM, Bogdan Nicula wrote: >> >> >> Thank you for your answer. >> Looking back to my original email, I believe I didn't explain well enough: I >> want to find the highest smaller and lowest greater numbers enclosing the >> number. >> For example, given the sequence: 1, 3, 5, 7, 9, etc., if I query for 6, I >> would like to get 5 and 7. >> >> Thanks,Bogdan >> >>> >>> Select max(number), min(number) from table; >>> >>> Wes >>> >>> On Mon, Jul 13, 2009 at 7:16 PM, Bogdan Nicula wrote: >>>> >>>> >>>> Hi, >>>> >>>> Sorry for my lack of SQL knowledge which triggered this help request: >>>> Given a column containing numbers, which is the most efficient manner to >>>> find out the highest smaller and lowest greater number? >>>> That is, is there a better way than: >>>> >>>> select * from table where number <= ? order by number desc limit 1; >>>> select * from table where number>= ? order by number asc limit 1; >>>> >>>> >>>> What if the table contains millions of rows? >>>> >>>> Thank you for any help,Bogdan >>>> _ >>> >> >> _ >> Share your memories online with anyone you want. >> http://www.microsoft.com/middleeast/windows/windowslive/products/photos-share.aspx?tab=1 >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _ With Windows Live, you can organize, edit, and share your photos. http://www.microsoft.com/middleeast/windows/windowslive/products/photo-gallery-edit.aspx ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] range enclosing a number
On 14 Jul 2009, at 12:16am, Bogdan Nicula wrote: > Given a column containing numbers, which is the most efficient > manner to find out the highest smaller and lowest greater number? > That is, is there a better way than: > > select * from table where number <= ? order by number desc limit 1; > select * from table where number>= ? order by number asc limit 1; Nope, that's a pretty good method. Except of course you need '>' not '>=' and '<' not '<='. Make sure, of course, that there's an index that starts with the 'number' field. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] range enclosing a number
Yeah, sorry about that. In two statements: select max(number) from table where number < ? select min(number) from table where number > ? if you want to merge them into a single statement, you can do: select (select max(number) from table where number < ?) highest_smaller, (select min(number) from table where number > ?) lowest_greater; On Mon, Jul 13, 2009 at 7:32 PM, Bogdan Nicula wrote: > > > Thank you for your answer. > Looking back to my original email, I believe I didn't explain well enough: I > want to find the highest smaller and lowest greater numbers enclosing the > number. > For example, given the sequence: 1, 3, 5, 7, 9, etc., if I query for 6, I > would like to get 5 and 7. > > Thanks,Bogdan > >> >> Select max(number), min(number) from table; >> >> Wes >> >> On Mon, Jul 13, 2009 at 7:16 PM, Bogdan Nicula wrote: >>> >>> >>> Hi, >>> >>> Sorry for my lack of SQL knowledge which triggered this help request: >>> Given a column containing numbers, which is the most efficient manner to >>> find out the highest smaller and lowest greater number? >>> That is, is there a better way than: >>> >>> select * from table where number <= ? order by number desc limit 1; >>> select * from table where number>= ? order by number asc limit 1; >>> >>> >>> What if the table contains millions of rows? >>> >>> Thank you for any help,Bogdan >>> _ >> > > _ > Share your memories online with anyone you want. > http://www.microsoft.com/middleeast/windows/windowslive/products/photos-share.aspx?tab=1 > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] range enclosing a number
Thank you for your answer. Looking back to my original email, I believe I didn't explain well enough: I want to find the highest smaller and lowest greater numbers enclosing the number. For example, given the sequence: 1, 3, 5, 7, 9, etc., if I query for 6, I would like to get 5 and 7. Thanks,Bogdan > > Select max(number), min(number) from table; > > Wes > > On Mon, Jul 13, 2009 at 7:16 PM, Bogdan Nicula wrote: >> >> >> Hi, >> >> Sorry for my lack of SQL knowledge which triggered this help request: >> Given a column containing numbers, which is the most efficient manner to >> find out the highest smaller and lowest greater number? >> That is, is there a better way than: >> >> select * from table where number <= ? order by number desc limit 1; >> select * from table where number>= ? order by number asc limit 1; >> >> >> What if the table contains millions of rows? >> >> Thank you for any help,Bogdan >> _ > _ Share your memories online with anyone you want. http://www.microsoft.com/middleeast/windows/windowslive/products/photos-share.aspx?tab=1 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] range enclosing a number
Sorry, I misread the question... Still, I think min/max are better than order by limit 1. Wes On Mon, Jul 13, 2009 at 7:24 PM, Wes Freeman wrote: > Select max(number), min(number) from table; > > Wes > > On Mon, Jul 13, 2009 at 7:16 PM, Bogdan Nicula wrote: >> >> >> Hi, >> >> Sorry for my lack of SQL knowledge which triggered this help request: >> Given a column containing numbers, which is the most efficient manner to >> find out the highest smaller and lowest greater number? >> That is, is there a better way than: >> >> select * from table where number <= ? order by number desc limit 1; >> select * from table where number>= ? order by number asc limit 1; >> >> >> What if the table contains millions of rows? >> >> Thank you for any help,Bogdan >> _ >> Show them the way! Add maps and directions to your party invites. >> http://www.microsoft.com/windows/windowslive/products/events.aspx >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] range enclosing a number
Select max(number), min(number) from table; Wes On Mon, Jul 13, 2009 at 7:16 PM, Bogdan Nicula wrote: > > > Hi, > > Sorry for my lack of SQL knowledge which triggered this help request: > Given a column containing numbers, which is the most efficient manner to find > out the highest smaller and lowest greater number? > That is, is there a better way than: > > select * from table where number <= ? order by number desc limit 1; > select * from table where number>= ? order by number asc limit 1; > > > What if the table contains millions of rows? > > Thank you for any help,Bogdan > _ > Show them the way! Add maps and directions to your party invites. > http://www.microsoft.com/windows/windowslive/products/events.aspx > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] range enclosing a number
Hi, Sorry for my lack of SQL knowledge which triggered this help request: Given a column containing numbers, which is the most efficient manner to find out the highest smaller and lowest greater number? That is, is there a better way than: select * from table where number <= ? order by number desc limit 1; select * from table where number>= ? order by number asc limit 1; What if the table contains millions of rows? Thank you for any help,Bogdan _ Show them the way! Add maps and directions to your party invites. http://www.microsoft.com/windows/windowslive/products/events.aspx ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
