Selecting closet numbers in a MySQL Database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Selecting closet numbers in a MySQL Database

Post by Mr Tech »

I can't seem to get my head around this problem...

Let's say I have a postcode search where you can enter your postcode and search a MySQL Database. I want to be able to pull out the 6 closet postcodes to the postcode searched for...

What query would I have to run for this? I was thinking something like the below query but I don't think it would work because it would pull out practically any number not just the closet:

Code: Select all

SELECT * FROM postcodes WHERE postcode='4213' or postcode<='4213' or postcode>='4213' LIMIT 10
I can't really use ORDER BY postcode asc because i need to search numbers less than the postcode and numbers greater than the postcode.

Anyone have any ideas for me?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Selecting closet numbers in a MySQL Database

Post by Eran »

order by the distance from the zipcode:

Code: Select all

SELECT *,ABS(zipcode - 4213) AS distance FROM postcodes ORDER BY distance LIMIT 10
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Selecting closet numbers in a MySQL Database

Post by VladSun »

viewtopic.php?f=2&t=92314

If it's enough for your needs I think it will be faster than using the ABS() function, because indexies are used for ORDER BY.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Selecting closet numbers in a MySQL Database

Post by Eran »

You can combine those, I didn't want to mention it because it gets ugly:

Code: Select all

SELECT *,ABS(zipcode - 4213) AS distance 
FROM (
    (SELECT * FROM postcodes WHERE zipcode >= 4213 ORDER BY zipcode LIMIT 11)
    UNION ALL
    (SELECT * FROM postcodes WHERE zipcode < 4213 ORDER BY zipcode DESC LIMIT 10)
) AS n ORDER BY distance LIMIT 10
 
However for a small table (a zipcode table for example - ~60k rows) the distance is very fast if the zipcode field is indexed.
I compared the approaches here - http://www.techfounder.net/2009/02/02/s ... -in-mysql/
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Selecting closet numbers in a MySQL Database

Post by VladSun »

Well done. Nice benchmarks :)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Re: Selecting closet numbers in a MySQL Database

Post by Mr Tech »

That's great! Thank you so much :)
Post Reply