Hi Dan, that's exactly what I do. But it's not that fast, I missed 2 '0''s, the table has 10000 rows (which is still not big, I think), but zip is only index. The "procedure" takes over a second, and that (that's the point) for every user and every query. But now I think it's the only way.
jan ----- Original Message ----- From: "Dan Nelson" <[EMAIL PROTECTED]> To: "Jan Peuker" <[EMAIL PROTECTED]> Cc: "Gurhan Ozen" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, July 22, 2002 11:35 PM Subject: Re: Surrounding Rows > 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 > [EMAIL PROTECTED] --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
