MySQL: group by and nearest integer -

i have following table

location:

userid |  zip  |  ----------------    1   | 12383 |    1   | 10253 |    2   | 10523 |    2   | 14856 |    2   | 10251 |    3   |  null |     

for given integer x, want sort user according has integer in column zip closest x, corresponding number of zip. if user has value null in field zip should shown @ end.

example: x = 5000, output

userid |  zip  |  ----------------    2   | 10251 |    1   | 10253 |    3   |  null |  

i managed userid's correctly sorted with:

select userid, min(abs(3-zip)) dist location group userid order -dist desc 

producing table

userid |  dist  |  -----------------    2   |  5251  |    1   |  5253  |    3   |  null  |  

but how can nearest zip code?

select t1.userid,        t1.zip location t1 inner join (     select userid, min(abs(3-zip)) dist     location     group userid ) t2     on t1.userid = t2.userid ,        (abs(3 - t1.zip) = t2.dist or t2.dist null)   -- pay careful attention here order -t2.dist desc                                  -- join on absolute difference 

demo here:

sqlfiddle

there few tricks in query:

• joining on abs(# - zip) ensures choose closest zip, regardless of whether higher or lower
• the t2.dist null condition in join includes user has null zip code
• using order -t2.dist desc sorts zip codes in ascending order, places null @ end of result set.