CODEWITHSUNDEEP
mysqljoin
kiran girase
kiran girase

Reputation: 97

MYsql select only rows with max value on a column for each record

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

User Table

User Location

Thanks in advance.

Upvotes: 2

Views: 48

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28874

  • Get Maximum value of LocationSyncDateTime for a User_Name in a Derived Table t3.
  • Join the main tables to this Derived table on 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

Related Questions