[SOLVED] Find the latest location of each users in a MySQL table

Issue

I have this table where I want to find the latest location of each user using an SQL query.

The table contains the columns, (1)email, (2)timestamp, (3)location.

So if the table looks like this:

email            timestamp    location
[email protected]  27.08.2014   kitchen
[email protected]  28.08.2014   livingroom
[email protected]  29.08.2014   kitchen
[email protected]  26.08.2014   hallway
[email protected]  27.08.2014   kitchen

I want it to output:

email            timestamp    location
[email protected]  29.08.2014   kitchen
[email protected]  27.08.2014   kitchen

Solution

SELECT yt.* FROM your_table yt
INNER JOIN (
    SELECT
    email, MAX(timestamp) max_ts
    FROM
    your_table 
    GROUP BY email
) sq ON yt.email = sq.email AND yt.timestamp = sq.max_ts

• find more examples of how to do it here: The Rows Holding the Group-wise Maximum of a Certain Column

Answered By – fancyPants

Answer Checked By – Clifford M. (BugsFixing Volunteer)