CODEWITHSUNDEEP
mysqlsql
Wazzbast
Wazzbast

Reputation: 23

MySQL select nearest location only if also latest location

Say I have 3 tables like so:

cities
------
name
latitude
longitude

stalls
------
id
name

locations
---------
stall_id
latitude
longitude
date

I want to find the nearest stalls to a city within the past 6 months. That part is fine - I query the stalls/locations table and use Haversine to get within distance. All good.

This is my 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)) ) ) AS distance
FROM locations
LEFT JOIN stalls ON stalls.id = locations.trader_id
WHERE locations.added > $date
HAVING distance < 10

However, it's possible for a stall to have multiple new locations in different cities and I only want to match to a city on the latest location. e.g. A stall may match as being closest to New York, but then a new location is added for LA. Now it should only show in LA for 6 months and not New York - at least until another location is added. Previous locations need to be stored so I can't just clear those.

What is the best way to do this in a query?

Upvotes: 0

Views: 84

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269993

To get the more recent location for each stall:

select l.*
from locations l
where l.date = (select max(l2.date) from locations l2 where l2.stall_id = l.stall_id);

I'm not sure how this fits into your overall query.

Upvotes: 1

Related Questions