Adam Adam - 4 months ago 8
MySQL Question

MySQL: group by and nearest integer

I have the following table

location:

userId | zip |
----------------
1 | 12383 |
1 | 10253 |
2 | 10523 |
2 | 14856 |
2 | 10251 |
3 | NULL |


For a given integer X, I want to sort the user according to who has a integer in column
zip
that is closest to X, together with the corresponding number of
zip
. If the user has value
NULL
in field
zip
then he should be shown at the end.

Example:
For X = 5000, I would like to get the output

userId | zip |
----------------
2 | 10251 |
1 | 10253 |
3 | NULL |


I managed to get the userId's correctly sorted with:

SELECT userId, MIN(ABS(3-zip)) as dist FROM location GROUP BY userId Order by -dist DESC


producing the table

userId | dist |
-----------------
2 | 5251 |
1 | 5253 |
3 | NULL |


but how can I also get the nearest zip code?

Answer
SELECT t1.userId,
       t1.zip
FROM location t1
INNER JOIN
(
    SELECT userId, MIN(ABS(3-zip)) AS dist
    FROM location
    GROUP BY userId
) t2
    ON t1.userId = t2.userId AND
       ABS(3 - t1.zip) = t2.dist     -- pay careful attention here
                                     -- join on the absolute difference