This article talks about selecting a row with a maximum date per user through different methods.
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:-
Frequently Asked:

Explanation:-
In figure 1.2, we got the latest record for each user. In the above query
- We are doing LEFT JOIN of user_details table with itself ON user_name and comparing the login_time at both sides.
- u1 and u2 are an alias for the user_details table on the left and the right side.
- 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:-
- 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.
- 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 !!!.
Cheers, I needed min date by users and this really helped me out! Not a robot comment either 🙂