Show only latest login from inner join SQL statement-CodePudding

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?

CodePudding user response：

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"

CodePudding user response：

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