title: How to get Subquery nearest distance?
body: <p>I have a query to get the distance after calculated based on latitude and longtitude store in database table.</p>
  
  <p>Each user had their own latitude and longtitude, I'm trying to get the centre name that are the nearest to the location of the user. But my query will having error when there are 2 location that is quite near to the user, how can i get the nearest centre name that can return in the subquery?</p>
  
  <p>Below are my PHP Query:</p>
  
  <pre><code>SELECT
  t1.id, t2.name AS na_name, t3.name AS centre_name, t1.login, t1.login_lat, t1.login_long,
          (SELECT centre_name  
          from centre_location where
          (3956 * 2 * ASIN(SQRT( POWER(SIN(( t1.login_lat - centre_lat) *  pi()/180 / 2), 2) +COS( t1.login_lat * pi()/180) * COS(centre_lat * pi()/180) * POWER(SIN(( t1.login_long - centre_long) * pi()/180 / 2), 2) ))) &lt;= 0.18
  ) AS LOGIN_LOCATION,  t1.login_location_accuracy,
          t1.logout,  t1.logout_lat, t1.logout_long, (SELECT centre_name  
          from centre_location where
          (3956 * 2 * ASIN(SQRT( POWER(SIN(( t1.logout_lat - centre_lat) *  pi()/180 / 2), 2) +COS( t1.logout_lat * pi()/180) * COS(centre_lat * pi()/180) * POWER(SIN(( t1.logout_long - centre_long) * pi()/180 / 2), 2) ))) &lt;= 0.18 
  ) AS LOGOUT_LOCATION, t1.logout_location_accuracy,  t1.attendance_device_type , t1.remark
  FROM attendance t1 
  left join user t2 on t1.user_id = t2.id
  left join centre t3 on t1.centre_id = t3.id
  where t1.id = 10130
  </code></pre>
  
  <p>I did try with ORDER BY and LIMIT 1, but the result is still not correct. Please refer below:</p>
  
  <pre><code>SELECT
  t1.id, t2.name AS na_name, t3.name AS centre_name, t1.login, t1.login_lat, t1.login_long,
          (SELECT centre_name  
          from centre_location where
          (3956 * 2 * ASIN(SQRT( POWER(SIN(( t1.login_lat - centre_lat) *  pi()/180 / 2), 2) +COS( t1.login_lat * pi()/180) * COS(centre_lat * pi()/180) * POWER(SIN(( t1.login_long - centre_long) * pi()/180 / 2), 2) ))) &lt;= 0.18
  ORDER BY (3956 * 2 * ASIN(SQRT( POWER(SIN(( t1.login_lat - centre_lat) *  pi()/180 / 2), 2) +COS( t1.login_lat * pi()/180) * COS(centre_lat * pi()/180) * POWER(SIN(( t1.login_long - centre_long) * pi()/180 / 2), 2) ))) &lt;= 0.18 DESC LIMIT 1
  ) AS LOGIN_LOCATION,  t1.login_location_accuracy,
          t1.logout,  t1.logout_lat, t1.logout_long, (SELECT centre_name  
          from centre_location where
          (3956 * 2 * ASIN(SQRT( POWER(SIN(( t1.logout_lat - centre_lat) *  pi()/180 / 2), 2) +COS( t1.logout_lat * pi()/180) * COS(centre_lat * pi()/180) * POWER(SIN(( t1.logout_long - centre_long) * pi()/180 / 2), 2) ))) &lt;= 0.18 
  ORDER BY (3956 * 2 * ASIN(SQRT( POWER(SIN(( t1.logout_lat - centre_lat) *  pi()/180 / 2), 2) +COS( t1.logout_lat * pi()/180) * COS(centre_lat * pi()/180) * POWER(SIN(( t1.logout_long - centre_long) * pi()/180 / 2), 2) ))) &lt;= 0.18 DESC LIMIT 1
  ) AS LOGOUT_LOCATION, t1.logout_location_accuracy,  t1.attendance_device_type , t1.remark
  FROM attendance t1 
  left join user t2 on t1.user_id = t2.id
  left join centre t3 on t1.centre_id = t3.id
  where t1.id = 10130
  </code></pre>
  
  <p>The centre_name should have only return 1 value so that I can display/export to the result that I want, but there was sometime the centre_name been return more than 1 row, how can I get the nearest value that return ?</p>