Latest Record for Each User in SQL Server

by
Latest record for each user in SQL Server

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.
Latest record for each user in SQL Server

Here is a sample table: EMPLOYEE_ACCESS which is created and displayed below:

ACCESS_IDUSER_NAMEDEPARTMENTLOGIN_TIMEEXIST_TIME
1sbrandt0Product Management18-02-202218-02-2022
2dmilesop1Product Management25-06-202225-06-2022
3jyeude2Legal01-01-202201-01-2022
4ndraye3Services08-06-202208-06-2022
5hbuntine4Training09-04-202209-04-2022
6kswinden5Human Resources06-08-202206-08-2022
7fevans6Legal21-08-202221-08-2022
8escopham7Services08-03-202208-03-2022
9kspary8Research and Development02-01-202202-01-2022
10rcarver9Research and Development13-11-202213-11-2022
11dmilesop1Product Management25-07-202225-07-2022
12jyeude2Legal01-01-202201-01-2022
13ndraye3Services08-06-202208-06-2022
14hbuntine4Training09-04-202209-04-2022
15kswinden5Human Resources06-08-202206-08-2022
EMPLOYEE_ACCESS table

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_NAMEMAX_TIME
dmilesop125-07-2022
escopham708-03-2022
fevans621-08-2022
hbuntine409-04-2022
jyeude201-01-2022
kspary802-01-2022
kswinden506-08-2022
ndraye308-06-2022
rcarver913-11-2022
sbrandt018-02-2022
Example of SQL Server MAX function with GROUP BY clause

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_IDUSER_NAMEDEPARTMENTLOGIN_TIMELOGIN_EXIST
11dmilesop1Product Management25-07-202225-07-2022
8escopham7Services08-03-202208-03-2022
7fevans6Legal21-08-202221-08-2022
5hbuntine4Training09-04-202209-04-2022
12jyeude2Legal01-01-202201-01-2022
9kspary8Research and Development02-01-202202-01-2022
6kswinden5Human Resources06-08-202206-08-2022
13ndraye3Services08-06-202208-06-2022
10rcarver9Research and Development13-11-202213-11-2022
1sbrandt0Product Management18-02-202218-02-2022
Example of Select the latest record of each user using the SQL Server JOINS

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.