'Find the latest location of each users in a MySQL table

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
mysqlsql


Solution 1:[1]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 fancyPants

Related Questions