> OK, that's clear.
Sorry about the bervity, ill clear this up below.
> Scott Haneda wrote:
>> I am thinking UNION and two SQL queries would do this, how is UNION
>> optimized, is it more or less the same as running two selects?
>
> Usually, but a UNION of what two queries? I won't comment on the efficiency
> of
> a query I haven't seen.
Here is what I was thinking:
(select zipcode FROM
zipcodes_head_of_house
WHERE zipcode >= '94949' ORDER BY zipcode
ASC LIMIT 1)
UNION
(select zipcode FROM
zipcodes_head_of_house
WHERE zipcode <= '94949' ORDER BY zipcode
DESC LIMIT 1)
This seems to give me either one of two records, in which case, its pretty
simple to find the closest one.
Here is my table structure, there is a lot more auxiliary data to it, but
these are the main bits that matter.
describe zipcodes_head_of_house;
+------------+---------------+------+-----+----------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+----------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| zipcode | char(5) | | MUL | | |
| latitude | double(12,6) | | | 0.000000 | |
| longitude | double(12,6) | | | 0.000000 | |
| created | timestamp(14) | YES | | NULL | |
+------------+---------------+------+-----+----------+----------------+
> This can be done in one query. You didn't give any details, so I'll make them
> up. The table is named scotts_table, the numbers are in the column named val,
> and the target value is 413. I'll use user variables for clarity, but they
> aren't necessary.
>
> For each row in the table, the distance from that row's val to the target
> value
> is the absolute value of the difference between val and the target value. The
> row with the smallest distance is the one you want. Hence,
>
> SET @target = 413;
>
> SELECT *
> FROM scotts_table
> ORDER BY ABS([EMAIL PROTECTED])
> LIMIT 1;
select zipcode from zipcodes_head_of_house order by abs(zipcode-94999) limit
1;
+---------+
| zipcode |
+---------+
| 95001 |
+---------+
I know 94999 is not in the database, and I get back 95001, which should be
the closest match, using my UNION to test it:
mysql> (select zipcode FROM
-> zipcodes_head_of_house
-> WHERE zipcode >= '94999' ORDER BY zipcode
-> ASC LIMIT 1)
-> UNION
-> (select zipcode FROM
-> zipcodes_head_of_house
-> WHERE zipcode <= '94999' ORDER BY zipcode
-> DESC LIMIT 1)
-> ;
+---------+
| zipcode |
+---------+
| 95001 |
| 94979 |
+---------+
2 rows in set (0.00 sec)
And there you are, the 95001 is of course the closest one.
I think this is it, this works well, and fast for me.
> Of course, that's a full-table scan with a filesort, so it's not very
> efficient.
> We can improve on this, however, if we know the size of the largest gap.
> For
> example, if we know that the largest gap is 26, we can do the following:
>
> SET @target = 413;
> SET @range=26;
>
> SELECT *
> FROM scotts_table
> WHERE val BETWEEN (@target - @range) AND (@target + @range)
> ORDER BY ABS([EMAIL PROTECTED])
> LIMIT 1;
I could probably figure it out, at some point, but right now, I have no idea
what the largest gap is.
> In this case, mysql can use the index on val (You do have an index on val,
> right?) to choose the few rows near the target value, before performing the
> filesort on just those few matching rows.
I am pretty sure I do, I will check though.
thanks
--
-------------------------------------------------------------
Scott Haneda Tel: 415.898.2602
<http://www.newgeo.com> Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
