Query to get nearest distance record

Question

I have following mysql query:

SELECT a.id
     , ( 3959 * acos( cos( radians(a.latitude) ) * cos( radians( w.latitude ) ) 
              * cos( radians( w.longitude ) - radians(-a.longitude) ) 
              + sin( radians(a.latitude) ) * sin(radians(w.latitude)) ) ) distance 
  FROM global_restaurants a 
  JOIN webgeocities w 
    ON w.name = a.locality 
   AND w.country_code = a.country 
   AND a.latitude LIKE w.latitude
  JOIN states s 
    ON s.state_code = w.state_code 
   AND w.country_code = s.country_code
 WHERE a.city_id = 0

After execution it returns:

id distance
70 6665.478678743614
70 6496.46971480875
70 6725.900646648246
70 6733.5156930808
90 6969.449661399672
90 7252.889875588891

I want it to only return 2 rows with minimum distance like:

id distance
70 6496.46971480875
90 6969.449661399672

here something that coul help you - http://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group ithink that group_concat, and find in set is cool hack — dabal, Sep 09 '16 at 11:58
I dont want distance in comma seperated format, I want only nearest distance record in single query. — RAC B, Sep 09 '16 at 12:05
And this is done by the last query in my example SELECT yourtable.* FROM yourtable INNER JOIN ( SELECT id, GROUP_CONCAT(year ORDER BY rate DESC) grouped_year FROM yourtable GROUP BY id) group_max ON yourtable.id = group_max.id AND FIND_IN_SET(year, grouped_year) BETWEEN 1 AND 5 ORDER BY yourtable.id, yourtable.year DESC; — dabal, Sep 09 '16 at 12:07

score 0 · Answer 1 · answered Sep 09 '16 at 12:19

0

add order by at the end of your query:

order by distance ASC LIMIT 2;

now you calculating the distance but at end of the query you must sort them and limit the returns row count..

.

NOTE:

i think you have a small mistake on your query the 3959 is for miles but i think the values are in meter

your full query can be like this:

select a.id, ( 6371000 * acos( cos( radians(a.latitude) ) * cos( radians( w.latitude ) ) 
* cos( radians( w.longitude ) - radians(-a.longitude) ) + sin( radians(a.latitude) ) * sin(radians(w.latitude)) ) ) AS distance 
from `global_restaurants` as a INNER JOIN webgeocities as w ON (w.name = a.locality AND w.country_code = a.country and a.latitude like w.latitude) INNER JOIN 
states AS s ON (s.state_code = w.state_code and w.country_code = s.country_code) where a.city_id = '0' ORDER BY distance ASC LIMIT 2;

answered Sep 09 '16 at 12:19

peiman F.

1,648
1
19
42

1

if i use order by then it return 2 rows. And that can be for the same id. I want all distinct id with minimum distance – RAC B Sep 09 '16 at 12:58
1

@RACB what is your meaning about `all distinct id with minimum distance` . mimum distans is one ..did you mean return all distance under for example 10 meter !?! for return just id you can specific it and your select `select a.id as thisId` – peiman F. Sep 09 '16 at 13:55
I mean every id has 4-5 distance i want unique id that result would be minimum distance row associated with that id. Currently I am getting- id distance 70 6665.478678743614 70 6496.46971480875 70 6725.900646648246 70 6733.5156930808 90 6969.449661399672 90 7252.889875588891 I want this- id distance 70 6496.46971480875 90 6969.449661399672 – RAC B Sep 12 '16 at 06:17

score -1 · Answer 2 · answered Sep 09 '16 at 14:11

For simplicity, let's assumed that you have your query in view - stack, then your query should look like this

select stack.* from stack join (
SELECT id, GROUP_CONCAT(distance ORDER BY 
distance asc) grouped_distance FROM stack GROUP BY id
) group_distance on group_distance.id=stack.id
where find_in_set(stack.distance,group_distance.grouped_distance)between 1 and 2;

Query to get nearest distance record

2 Answers2