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

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

Re: [sqlite] range enclosing a number

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: >&g

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

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

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

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

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: > > selec

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; >

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)

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.

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

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

[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