CODEWITHSUNDEEP
mysql
FunkyMonk91
FunkyMonk91

Reputation: 1481

MySQL SELECT MAX datetime and information related to it

I am trying to get the last time and location that a user has logged in. I have 2 tables, a Users table and User_Logins table.

Users Table

id    INT
fname VARCHAR
lname VARCHAR
team  VARCHAR

User_Logins Table

id        INT
user_id   VARCHAR
location  VARCHAR
date_time DATETIME

Query

SELECT u.id, u.fname, u.lname, ul.location, MAX(ul.time_accessed) AS last_login_time
FROM Users AS u 
  INNER JOIN User_Logins AS ul
    ON u.id = ul.user_id
WHERE team = 'TEST-TEAM-1'
GROUP BY u.id

Now this works as far as getting the last login time for each user on the team, but it doesn't give me the correct location. How would I go about getting both the last_login_time and login location?

Thanks!

Upvotes: 0

Views: 1762

Answers (1)

radar
radar

Reputation: 13425

you need to use a subquery and get the maximum accessed value per each user id and then join again with users table to get details.

SELECT * FROM
Users U
JOIN 
(SELECT u.id, MAX(ul.time_accessed) AS last_login_time
FROM Users AS u 
  INNER JOIN User_Logins AS ul
    ON u.id = ul.user_id
WHERE team = 'TEST-TEAM-1'
GROUP BY u.id ) T
on U.id = T.id
JOIN User_Logins ul
on ul.user_id = T.id
and ul.time_accessed = T.last_login_time

Upvotes: 1

Related Questions