Show only latest login from inner join SQL statement

user_18992708 0
Viewed 27

I'm relatively new to SQL and I have the following query to get a list of logins since Jan 1st. I'm trying to only display each user's last login.


SELECT  usrlogs.serverlogintime AS Login_Date,
    usrlogs.usrname         AS User_Name,
    usrlogs.usrid           AS User_ID,
    usrlogs.usrlogid        AS Log_ID,
    users.status            AS Active
FROM   usrlogs
       INNER JOIN users
               ON usrid = uid
WHERE  DATE_FORMAT (ServerLoginTime,'%Y-%m-%d')  >= '2022-01-01' and status="0"

User_Log_ID increases by 1 with each new login to the server. Is there a way to only display each user's highest Log ID?

sql
2 Answers

You need to use Row_Number() like this:

SELECT  * FROM (
SELECT  usrlogs.serverlogintime AS Login_Date,
    usrlogs.usrname         AS User_Name,
    usrlogs.usrid           AS User_ID,
    usrlogs.usrlogid        AS Log_ID,
    users.status            AS Active,
    Row_number() over (partition by usrlogs.usrid order by usrlogs.usrlogid desc ) rw
FROM   usrlogs
       INNER JOIN users
               ON usrid = uid
WHERE  DATE_FORMAT (ServerLoginTime,'%Y-%m-%d')  >= '2022-01-01' and status="0"
) t where t.rw=1
user_60231730

you can subselebt the higest logdid from the user and select the userlogs with that id

SELECT  u.serverlogintime AS Login_Date,
    u.usrname         AS User_Name,
    u.usrid           AS User_ID,
    u.usrlogid        AS Log_ID,
    users.status            AS Active
FROM   usrlogs u
INNEr JOIN (SELECT MAX(usrlogid) as usrlogid,usrid FROM usrlogs GROUP BY usrid) u1 ON u1.usrid = u.usrid AND u1.usrlogid = u.usrlogid
       INNER JOIN users
               ON u.usrid = users.uid
WHERE  DATE_FORMAT (u.ServerLoginTime,'%Y-%m-%d')  >= '2022-01-01' and status="0"
user_51935360
Related