finding near misses in mysql

anyone have a slick way?

[ergophobe]

I would like to return a single result set consisting of two separate rows containing the next lesser and the next greater value to a value that returns no result.

The only way I can think of for the present is to use multiple queries and a temporary table like so:

CREATE TEMPORARY TABLE nearest SELECT id FROM table1 WHERE id>7;
ORDER BY id LIMIT 1;
INSERT INTO nearest (id) SELECT id FROM table1 WHERE id<7 ORDER BY id DESC LIMIT 1;
SELECT * FROM nearest;

Is there a better way to do this in MySQL 3.23.x?

One thing to add - I don't want merely the two closest results, that I could get with

select id, min(abs(id-7)) as closest from mytable group by id, order by closest limit 2

Tom

[coopster]

First off, I'm assuming you have run a query and returned an empty result set, that is why you are now trying to get the next lesser and next greater values. That said, you could JOIN the table to itself and return the values you want, but they will be in a single row as opposed to the two separate rows you requested. Would this work for you?

$sql =  
"SELECT  
MAX(t1.id)AS next_lesser,  
MIN(t2.id) AS next_greater  
FROM mytable AS t1, mytable AS t2  
WHERE t1.id<$myvalue AND t2.id>$myvalue; 

If your table had values something like...

ID 
---- 
<snip> 
3 
4 
5 
6 
9 
11 
12 
<snip> 

...and $myvalue was 7, the result set would return:

+-------------+--------------+ 
¦ next_lesser ¦ next_greater ¦ 
+-------------+--------------+ 
¦           6 ¦            9 ¦ 
+-------------+--------------+ 

[ergophobe]

First off, I'm assuming you have run a query and returned an empty result set, that is why you are now trying to get the next lesser and next greater values.

Correct.

a single row as opposed to the two separate rows you requested. Would this work for you?

Thanks, but no, it doesn't really work; I really do need it on two rows. Otherwise the temp table solution is a lot less work, because all the functionality already exists for processing and displaying the results from a successful query, which of course have one record id per row.

It is perhaps not possible, and the temp table solution works fine, but it seems messy since it requires three or four queries (depending on whether you use the "DROP IF EXISTS" query):

drop mytable if it exists
create temp mytable... select next lowest
insert into mytable next highest
select * from mytable

Tom

[jatar_k]

well if it is 3 or 4 queries it is easy to do in 2

select * from table where id > 7 order by id asc limit 1
select * from table where id < 7 order by id desc limit 1

[ergophobe]

Nope. Still not the same thing. I want to send *a* (just one) result set somewhere and process that. With two selects, I have to send two result sets, and that would, like the single-row solution require rewriting the processing and display logic.