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

Question

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"

Data from table "users"

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:

My expectation is below:

Answer 1

Try:

select * from (select sessions.USERNAME, sessions.clientname as "CLIENT NAME", sessions.version as "VERSION", users.TASK, sessions.LOGINTIME as "LOGIN TIME", ROW_NUMBER() OVER (partition by sessions.UserName, sessions.ClientName ORDER BY sessions.LoginTime DESC) as Rn
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')) X WHERE Rn = 1

This should work in Oracle.

Answer 2

Hi @Smile_28
First, SQLServer doesn't have TO_DATE function. You have to use convert in SQL Sever. Please refer to this link for more details.

Second,
the reason why you found two records for the clientname TEST_A_1 is because there is group by sessions.version in your groupby statement, so when a clientname has several different versions, there will be several different records.

Third, there are several ways to deal with this issue:
You can use Cross Apply like this:

SELECT DISTINCT C.USERNAME,C.CLIENTNAME AS "CLIENT NAME",C.VERSION,U.TASK,C.LOGINTIME AS "LOGIN TIME"  
FROM users U RIGHT JOIN sessions S on U.USERNAME = S.USERNAME  
              CROSS APPLY(SELECT TOP(1) USERNAME,CLIENTNAME,VERSION,LOGINTIME FROM sessions WHERE USERNAME=S.USERNAME AND CLIENTNAME=S.CLIENTNAME ORDER BY LOGINTIME DESC )C  
WHERE S.USERNAME = 'TEST_PROD' AND S.LOGINTIME >='15-FEB-22'  

Or you can have a try on ROW_NUMBER function, like this:

;WITH CTE AS  
(SELECT *,ROW_NUMBER()OVER(PARTITION BY UserName,ClientName ORDER BY LOGIN_TIME DESC)AS RNum  
 FROM #sessions  
)SELECT DISTINCT C.USERNAME,C.CLIENTNAME AS "CLIENT NAME",C.VERSION,U.TASK,C.LOGIN_TIME AS "LOGIN TIME"  
FROM #users U RIGHT JOIN CTE C ON U.USERNAME = C.USERNAME  
WHERE C.USERNAME = 'TEST_PROD' AND C.LOGIN_TIME >='15-FEB-22' AND C.RNum=1  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.