Reputation: 97
I have user location and user table in MySql.
Now what I want is, I want to fetch Full_Name , Mobile_Number,
Latitude, Longitude, LocationSyncDateTime for last inserted or
latest record against each Username according to
LocationSyncDateTime.
below i have attached screenshots of my table
Thanks in advance.
Upvotes: 2
Views: 48
Reputation: 28874
LocationSyncDateTime
for a User_Name
in a Derived Table t3
. User_Name
and max value of LocationSyncDateTime
.Try the following:
SELECT u.Full_Name,
u.Mobile_Number,
l.Latitude,
l.Longitude,
l.LocationSyncDateTime
FROM user AS u
JOIN location AS l ON l.User_Name = u.User_Name
JOIN (SELECT l2.User_Name,
MAX(l2.LocationSyncDateTime) AS LocationSyncDateTime
FROM location AS l2
GROUP BY l2.User_Name
) AS t3 ON t3.User_Name = l.User_Name
AND t3.LocationSyncDateTime = l.LocationSyncDateTime
Upvotes: 3