'How do I query most recent record data based on login datetime in SQL

I have two tables to join and show detail of the recent date based on login time. Could you please advise?

Data from table "sessions"

USERNAME CLIENT NAME VERSION LOGIN TIME
TEST_PROD TEST_A_1 2.1.2 7-Mar-22
TEST_PROD TEST_A_1 2.1.1 15-FEB-22
TEST_PROD TEST_B 3.1.1 7-Mar-22
TEST_PROD TEST_C 4.1.3 27-FEB-22
TEST_PROD TEST_D 4.1.5 27-FEB-22

Data from table "users"

USERNAME TASK
TEST_PROD messenger
TEST_PROD messenger
TEST_PROD messenger
TEST_PROD messenger
TEST_PROD messenger

I executed below SQL query:

select sessions.USERNAME, sessions.clientname as "CLIENT NAME", sessions.version as "VERSION", users.TASK, MAX(sessions.LOGINTIME) as "LOGIN TIME"
FROM users
RIGHT JOIN sessions on users.USERNAME = sessions.USERNAME WHERE sessions.USERNAME = 'TEST_PROD' AND sessions.LOGINTIME >= to_date('15-FEB-22','DD-MON-YY')
GROUP BY sessions.USERNAME, sessions.clientname, sessions.version, users.TASK;

I got below result:

USERNAME CLIENT NAME VERSION TASK LOGIN TIME
TEST_PROD TEST_A_1 2.1.2 messenger 7-Mar-22
TEST_PROD TEST_A_1 2.1.1 messenger 15-FEB-22
TEST_PROD TEST_B 3.1.1 messenger 7-Mar-22
TEST_PROD TEST_C 4.1.3 messenger 27-FEB-22
TEST_PROD TEST_D 4.1.5 messenger 27-FEB-22

My expectation is below:

USERNAME CLIENT NAME VERSION TASK LOGIN TIME
TEST_PROD TEST_A_1 2.1.2 messenger 7-Mar-22
TEST_PROD TEST_B 3.1.1 messenger 7-Mar-22
TEST_PROD TEST_C 4.1.3 messenger 27-FEB-22
TEST_PROD TEST_D 4.1.5 messenger 27-FEB-22
sql


Solution 1:[1]

If I understood correctly what you are attempting to do, you could SELECT DISTINCT ON (username) and ORDER BY LOGINTIME DESC to get your expected results.

select distinct on (sessions.username) sessions.USERNAME, sessions.clientname as "CLIENT NAME", sessions.version as "VERSION", users.TASK, MAX(sessions.LOGINTIME) as "LOGIN TIME"
FROM users
RIGHT JOIN sessions on users.USERNAME = sessions.USERNAME WHERE sessions.USERNAME = 'TEST_PROD' AND sessions.LOGINTIME >= to_date('15-FEB-22','DD-MON-YY')
GROUP BY sessions.USERNAME, sessions.clientname, sessions.version, users.TASK
ORDER BY sessions.LOGINTIME DESC;

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 Lauri

Related Questions