List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:July 22 2002 9:35pm
Subject:Re: Surrounding Rows
View as plain text  
In the last episode (Jul 22), Jan Peuker said:
> What I want to do is, select the 10(say, a value) nearest values to a
> given number. The easiest way would be "near 5" shows 1..10. The
> problem is, the number should affect rows, not values, because values
> are not unique nor in order. What I want to do is a query like
> "SELECT code FROM zip WHERE zip=12345 SURROUND 5,5" or "SURROUND 10".
> At the moment, I create a new table w/ an autoincrement and do a
> select...insert, then I select first the id and then id-5 and id+5.
> This a a) very slow and redundant b) afaik not very safe.

So what you really want is

CREATE TEMPORARY TABLE tmp
 SELECT code FROM mytable WHERE zip < 12345 ORDER BY zip DESC LIMIT 5;
INSERT INTO tmp
 SELECT code FROM mytable WHERE zip = 12345;
INSERT INTO tmp
 SELECT code FROM mytable WHERE zip > 12345 ORDER BY zip LIMIT 5;
SELECT code FROM tmp ORDER BY zip;
DROP TABLE tmp;

That will use the zip index, so it should run fast.

-- 
	Dan Nelson
	dnelson@stripped
Thread
Surrounding RowsJan Peuker22 Jul
  • Re: Surrounding RowsGurhan Ozen22 Jul
  • Re: Surrounding RowsJan Peuker22 Jul
    • Re: Surrounding RowsDavid Felio22 Jul
    • RE: Surrounding RowsGordon22 Jul
    • Re: Surrounding RowsDan Nelson22 Jul
  • Re: Surrounding RowsGurhan Ozen22 Jul
  • Re: Surrounding RowsJan Peuker23 Jul
    • Re: Surrounding RowsDan Nelson23 Jul