CODEWITHSUNDEEP
mysql
wwl
wwl

Reputation: 2065

Get last entry for each user in a MySQL table

I have an SQL table user_login with two columns, userId and tstamp. Each entry contains the userId of the user who logged in, as well as the associated timestamp. If a user logged in 6 times, then there are six entries associated with the user.

It's easy to obtain a list of users who logged in after a certain date, e.g.

SELECT DISTINCT userId FROM user_login WHERE tstamp > '2017-10-01 00:00:00' 
GROUP BY userId HAVING COUNT(*) > 0;

How can I generate a table with two columns: userId and lastLoginDate i.e. the last login date?

Upvotes: 0

Views: 25

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176284

You could use:

SELECT userId, MAX(tstamp) AS lastLoginDate
FROM user_login 
WHERE tstamp > '2017-10-01 00:00:00' 
GROUP BY userId;

Upvotes: 1

Related Questions