MySQL select row with max date per user [Solved]

This article talks about selecting a row with a maximum date per user through different methods.

1. Select row with max date per user using JOINS
2. Select row with max date per user using MAX() function

Let us get started by creating a table and inserting data into it, which we will be using across this article.

#create the table
CREATE TABLE user_details (
    id INT,
    user_name VARCHAR(255),
    login_time datetime
);
#insert data into the table
INSERT INTO user_details (id,user_name,login_time) 
VALUES(1, "Henry",'2020-11-05 14:29:36');
INSERT INTO user_details (id, user_name,login_time) 
VALUES(2, "Richa",'2020-12-06 15:29:36'); 
INSERT INTO user_details (id, user_name,login_time) 
VALUES(1, "Henry",'2020-12-12 14:30:36');
INSERT INTO user_details (id,user_name,login_time) 
VALUES(3, "George",'2020-10-12 14:39:45');
INSERT INTO user_details (id,user_name,login_time) 
VALUES(2, "Richa",'2021-01-01 13:29:26');
INSERT INTO user_details (id,user_name,login_time) 
VALUES(4, "Ved",'2021-01-02 14:40:36');
INSERT INTO user_details (id,user_name,login_time) 
VALUES(2,"Richa",'2021-01-02 12:30:30');
INSERT INTO user_details (id,user_name,login_time) 
VALUES(1, "Henry",'2020-10-05 11:19:36');
INSERT INTO user_details (id, user_name,login_time) 
VALUES(4, "Ved",'2019-11-05 12:31:31');
INSERT INTO user_details (id, user_name,login_time) 
VALUES(3,"George",'2019-11-05 11:39:36');
INSERT INTO user_details (id,user_name,login_time) 
VALUES(4, "Ved",'2021-01-07 10:10:16');
INSERT INTO user_details (id,user_name,login_time) 
VALUES(3, "George",'2021-01-07 11:11:33');

SELECT * FROM user_details;

Select row with max date per user using JOINS

The table user_details has columns id, user_name, and login_time. We will be looking into how to get the row with the latest login_time per user_name using a LEFT JOIN. 

LEFT JOIN returns all the rows from the LEFT side of the table in the query and the matching results from the table’s right side in the query. Observe the below query.

SELECT 
    u1.*
FROM
    user_details AS u1
        LEFT JOIN
    user_details AS u2 ON u1.user_name = u2.user_name
        AND u1.login_time < u2.login_time
WHERE
    u2.login_time IS NULL
        AND u1.login_time IS NOT NULL;

Output:-

Explanation:-

In figure 1.2, we got the latest record for each user. In the above query

1. We are doing LEFT JOIN of user_details table with itself ON user_name and comparing the login_time at both sides.
2. u1 and u2 are an alias for the user_details table on the left and the right side.
3. Finally, select all the columns from u1. The WHERE clause ignores all the rows with null values of login_time from u1.

Select row with max date per user using MAX() function

Another way to get the latest record per user is using inner queries and Max() function. 

Max() function, when applied on a column, gives the maximum value of that column. Observe the below query.

SELECT 
    u1.*
FROM
    user_details u1
WHERE
    u1.login_time = (SELECT 
            MAX(u2.login_time)
        FROM
            user_details u2
        WHERE
            u2.user_name = u1.user_name);

Output:-

We have the same results in figure 1.3 as in figure 1.2 that is the latest record per user.

Explanation:-

1. Select all the columns from the user_details table WHERE the login_time in outer query matches the login_time retrieved from the inner query.
2. Inner query: gets the max (login_time) from user_details table, WHERE condition on user_name ensures to get one row for each user_name.

We hope this article helped you to get the most recent record per user in a table. Good Luck !!!.