Selecting a range of +10 and -10 1

[jimoblak]

I have a USPS zip code table that shows zip, city, state, latitude, and longitude. I hope to allow a person to enter a zip code to find other locations near them. While I can play around with formulas of latitude and longitude for proximity searches, I want to keep things simple by searching a range of zip codes (which are typically adjacent).

If I am searching for 10 zipcodes on either side of '64430', I could do...

SELECT *
FROM zipcodes
WHERE zipcodes.zip >= 64420
AND zipcodes.zip <= 64440;

But this does not guarantee 10 results on either side of the zip code of 64430 since there is no zipcode of 64425. Can someone suggest the ideal SQL?

 

[TonyGroves]

How about:
[tt]
SELECT *
FROM zipcodes
WHERE zip>=64430
ORDER BY zip
LIMIT 11
UNION
SELECT *
FROM zipcodes
WHERE zip<64430
ORDER BY zip DESC
LIMIT 10
[/tt]

 

[TonyGroves]

P.S. You might need to refine it a bit:
[tt]
(
SELECT *
FROM zipcodes
WHERE zip>=64430
ORDER BY zip
LIMIT 11
)
UNION
(
SELECT *
FROM zipcodes
WHERE zip<64430
ORDER BY zip DESC
LIMIT 10
)
ORDER BY zip
[/tt]

 

[jimoblak]

Thanks - I tried a union before but it did not work. I just realized why - the server I am developing for is stuck on MySQL 3.23 so I will have to dumb this down a bit. Suggestions for vintage MySQL are still welcome.

 

[TonyGroves]

To get the same result as the UNION, you could use a temporary table, selecting into the table twice, then selecting from the table.

Alternatively, you could use something like:
[tt]
SELECT *
FROM zipcodes
ORDER BY ABS(zip-64430)
LIMIT 21
[/tt]
which will give the nearest 21 zipcodes to 64430. However, that's not quite what you're looking for.