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

[Jay Blanchard]

Howdy gurus!

I have an interesting problem, that may be quite simple to solve but I am
not seeing it. I need to select from a table the 3 numbers above, and 3
numbers below a value. Here is the sample table;

mysql> select * from ErlangTest;
+----+-----------+
| id | erlang_no |
+----+-----------+
|  1 |   0.00001 |
|  2 |   0.00567 |
|  3 |   0.00883 |
|  4 |   1.00017 |
|  5 |   1.00238 |
|  6 |   1.06565 |
|  7 |   1.08970 |
|  8 |   2.00040 |
|  9 |   2.00856 |
| 10 |   2.03468 |
+----+-----------+

and I try;

mysql> select * from ErlangTest
    -> where erlang_no <= '1.5'
    -> limit 3;
+----+-----------+
| id | erlang_no |
+----+-----------+
|  1 |   0.00001 |
|  2 |   0.00567 |
|  3 |   0.00883 |
+----+-----------+

But I want to get;
+----+-----------+
| id | erlang_no |
+----+-----------+
|  5 |   1.00238 |
|  6 |   1.06565 |
|  7 |   1.08970 |
+----+-----------+

which are the 3 numbers directly below the amount that has been queried. To
get one number I can specify MAX

mysql> select max(erlang_no) from ErlangTest
    -> where erlang_no <= '1.5';

returning;

+----------------+
| max(erlang_no) |
+----------------+
|        1.08970 |
+----------------+

Will I have to programatically loop through setting the erlang_no in the
WHERE clause, setting it to the last retrieved value (which I can do easily,
as I have already tested it)? Or is there a way to do this in the SQL
statement, saving me from having to re-query the database for each
subsequent value?

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

***********************************************************
* Texas PHP Developers Conf  Spring 2003                  *
* T Bar M Resort & Conference Center                      *
* New Braunfels, Texas                                    *
* San Antonio Area PHP Developers Group                   *
* Contact jay.blanchard at niicommunications.com             *
*                                                         *
* Want to present a paper or workshop? Contact now!       *
***********************************************************