[thelist] selecting above and below ranges in a SQL query [long]

[Chris Blessing]

Jay, it's been a while since I got into it with mysql, but I believe you'll
have to split this out into two queries (unless you use MySQL 4, then skip
down to that paragraph below).  Your first would pull the 3 results directly
after the target number:

mysql> select id, erlang_no from ErlangTest
	-> where erlang_no < '1.5'
	-> limit 3
	-> order by erlang_no
	-> desc;

Using the desc keyword, you can have the results come back at you backwards,
and therefore you'll get the 3 results directly "below" the target number
you were looking for, as oppposed to the first 3 results from the result
set.  Verbally this is the equivalent of saying "get 3 numbers less than 1.5
starting at 1.5 instead of the lowest number available".

To get the other 3 results, just do a:

mysql> select id, erlang_no from ErlangTest
	-> where erlang_no > '1.5'
	-> limit 3
	-> order by erlang_no;

This is of course all untested, so my apologies if I'm completely off here.
It happens all too frequently for me. :)

If you wanted to see if a row existed for the value specifically, you would
do a 3rd query for:

mysql> select id, erlang_no from ErlangTest
	-> where erlang_no = '1.5';

If course if you have MySQL 4.0.0+, you could UNION them all together to get
ONE result set:

mysql> (select id, erlang_no from ErlangTest
	-> where erlang_no < '1.5'
	-> limit 3
	-> order by erlang_no
	-> desc)
	-> UNION
	-> select id, erlang_no from ErlangTest
	-> where erlang_no = '1.5'
	-> UNION
	-> (select id, erlang_no from ErlangTest
	-> where erlang_no > '1.5'
	-> limit 3
	-> order by erlang_no)

Let me know if this actually works, I'm pretty sure I'm off on one thing or
another here. :)

Chris Blessing
webguy at mail.rit.edu
http://www.330i.net


> The desired outcome is to get the one equal to (if there is a
> match), the 3
> above, and the 3 below that value, so that the value stated in the query
> (1.5) returns (there is no row matching 1.5);
>
> +----+-----------+
> | id | erlang_no |
> +----+-----------+
> |  5 |   1.00238 |
> |  6 |   1.06565 |
> |  7 |   1.08970 |
> |  8 |   2.00040 |
> |  9 |   2.00856 |
> | 10 |   2.03468 |
> +----+-----------+
>
> Any help or insight would be appreciated. And as always, a great
> big thinks
> in advance.
>
> Jay