In this SQL Server tutorial, we will understand how to get the latest record for each user from a table in a SQL Server.
There are 2 ways to fetch the latest record for each user in SQL Server. And in this tutorial, we will discuss both methods:
- Using the SQL Server MAX function
- Using the SQL Server JOINS.

Here is a sample table: EMPLOYEE_ACCESS which is created and displayed below:
ACCESS_ID | USER_NAME | DEPARTMENT | LOGIN_TIME | EXIST_TIME |
1 | sbrandt0 | Product Management | 18-02-2022 | 18-02-2022 |
2 | dmilesop1 | Product Management | 25-06-2022 | 25-06-2022 |
3 | jyeude2 | Legal | 01-01-2022 | 01-01-2022 |
4 | ndraye3 | Services | 08-06-2022 | 08-06-2022 |
5 | hbuntine4 | Training | 09-04-2022 | 09-04-2022 |
6 | kswinden5 | Human Resources | 06-08-2022 | 06-08-2022 |
7 | fevans6 | Legal | 21-08-2022 | 21-08-2022 |
8 | escopham7 | Services | 08-03-2022 | 08-03-2022 |
9 | kspary8 | Research and Development | 02-01-2022 | 02-01-2022 |
10 | rcarver9 | Research and Development | 13-11-2022 | 13-11-2022 |
11 | dmilesop1 | Product Management | 25-07-2022 | 25-07-2022 |
12 | jyeude2 | Legal | 01-01-2022 | 01-01-2022 |
13 | ndraye3 | Services | 08-06-2022 | 08-06-2022 |
14 | hbuntine4 | Training | 09-04-2022 | 09-04-2022 |
15 | kswinden5 | Human Resources | 06-08-2022 | 06-08-2022 |
Get latest record for each user in SQL Server using MAX()
In this section, we will understand how to fetch the latest record for each user in the employee_access table using Max() function.
The SQL Server MAX() function is used to retrieve the max record of the column_name or expression from the table. So, we will use the MAX() function on the LOGIN_TIME column to get only unique usernames with the most recent login time.
Try to observe the following query:
EXAMPLE:
SELECT USER_NAME, MAX(LOGIN_TIME) AS MAX_TIME
FROM EMPLOYEE_ACCESS
GROUP BY USER_NAME;
In the EMPLOYEE_ACCESS table, we retrieve the user name of the employee with the maximum login time using the GROUP BY clause.
USER_NAME | MAX_TIME |
dmilesop1 | 25-07-2022 |
escopham7 | 08-03-2022 |
fevans6 | 21-08-2022 |
hbuntine4 | 09-04-2022 |
jyeude2 | 01-01-2022 |
kspary8 | 02-01-2022 |
kswinden5 | 06-08-2022 |
ndraye3 | 08-06-2022 |
rcarver9 | 13-11-2022 |
sbrandt0 | 18-02-2022 |
So, in this section, we understood how to get the latest record for each user using the MAX() and GROUP BY.
Read: How to check if SQL Server is running
Fetch latest record for each user in SQL Server using JOIN
In this section, we will understand how to use the SQL Server INNER JOIN with the MAX() to fetch the latest record for each user in SQL Server.
Try to observe the following query:
EXAMPLE:
SELECT E1.*
FROM EMPLOYEE_ACCESS E1
JOIN ( SELECT USER_NAME,
MAX(LOGIN_TIME) MAX_LOGIN_EXIST
FROM EMPLOYEE_ACCESS
GROUP BY USER_NAME) E2
ON E1.USER_NAME=E2.USER_NAME
AND E1.LOGIN_TIME=E2.MAX_LOGIN_EXIST
ORDER BY E1.USER_NAME;
With the help of the INNER JOIN in the SELECT statement, we retrieve all records of the alias table E1 as the EMPLOYEE_ACCESS table. Using another alias table E2 on EMPLOYEE_ACCESS, we get the maximum login time and user name for the employee.
Then we checked table E1 login time which is equal to table E2 max login time. In addition, we arranged the results in ascending order according to the E1 user’s name.
ACCESS_ID | USER_NAME | DEPARTMENT | LOGIN_TIME | LOGIN_EXIST |
11 | dmilesop1 | Product Management | 25-07-2022 | 25-07-2022 |
8 | escopham7 | Services | 08-03-2022 | 08-03-2022 |
7 | fevans6 | Legal | 21-08-2022 | 21-08-2022 |
5 | hbuntine4 | Training | 09-04-2022 | 09-04-2022 |
12 | jyeude2 | Legal | 01-01-2022 | 01-01-2022 |
9 | kspary8 | Research and Development | 02-01-2022 | 02-01-2022 |
6 | kswinden5 | Human Resources | 06-08-2022 | 06-08-2022 |
13 | ndraye3 | Services | 08-06-2022 | 08-06-2022 |
10 | rcarver9 | Research and Development | 13-11-2022 | 13-11-2022 |
1 | sbrandt0 | Product Management | 18-02-2022 | 18-02-2022 |
Read: MySQL vs SQL Server
Conclusion
So, in this SQL Server tutorial, we discussed the following 2 methods to get the recent record for each user with examples
- Fetch latest record for each user in SQL using MAX()
- Get latest record for each user in SQL Server using JOIN
You may also like to read the following SQL Server tutorials.
- How to select latest record in SQL Server
- SQL Server logical operators and example
- Find Stored Procedure by Name in SQL Server
- Arithmetic operators in SQL Server
- Select last 10 records in SQL Server without sorting
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.