sql - MySQL select nearest location only if also latest location -
say have 3 tables so:
cities ------ name latitude longitude stalls ------ id name locations --------- stall_id latitude longitude date
i want find nearest stalls city within past 6 months. part fine - query stalls/locations table , use haversine within distance. good.
this current query:
select stalls.name, ( 3959 * acos( cos( radians($lat) ) * cos( radians( locations.lat ) ) * cos( radians( locations.lng ) - radians($lng) ) + sin( radians($lat) ) * sin(radians(locations.lat)) ) ) distance locations left join stalls on stalls.id = locations.trader_id locations.added > $date having distance < 10
however, it's possible stall have multiple new locations in different cities , want match city on latest location. e.g. stall may match being closest new york, new location added la. should show in la 6 months , not new york - @ least until location added. previous locations need stored can't clear those.
what best way in query?
to more recent location each stall:
select l.* locations l l.date = (select max(l2.date) locations l2 l2.stall_id = l.stall_id);
i'm not sure how fits overall query.
Comments
Post a Comment