Fetch recent item from each and every group

Question

In MySQL query, I wanted to query a list of recent login 1 user information form each user type.

Table Information: user table has columns: user_id, usertype_id, last_login_date usertype table has columns: usertype_id, user_id

I have tried,

SELECT MAX(u.last_login_date), u.user_id FROM `user` u
JOIN usertype ut ON u.usertye_id = ut.usertype_id
GROUP BY ut.usertype_id;

But the above query returns 'last_login_date' irrespective of the user_id. Any ideas will be appreciated.

Possible duplicate of [Get records with max value for each group of grouped SQL results](https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results) — Juan Carlos Oropeza, Sep 20 '17 at 19:38
I added the [tag:greatest-n-per-group] tag. This type of question has come up frequently, and there are many answers available. You should go read some and see if they give the solution you need. — Bill Karwin, Sep 20 '17 at 19:41

score 0 · Answer 1 · answered Sep 20 '17 at 20:48

select  *
from    (select  u.last_login_date, u.user_id 
         from    user u
         order by u.last_login_date desc) x
group by last_login_date

With this solution you can avoid complicated subqueries that try to find the max() etc, and also the problems of returning multiple rows when there are more than one with the same maximum value

Fetch recent item from each and every group

1 Answers1