Show only latest login from inner join SQL statement

July 27, 2022

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?

>Solution :

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"