How do I write a MySQL query to group results while preserving IDs of particular rows?

Question

I’m using MySQL 5.5.37. I’m having trouble writing a query. I have the following columns of data …

select distinct u.id, u.first_name, u.last_name, l.last_login_time …

What I want is to select one row where the first name and last name are unique per the most recent last_login_time. So if the above query returns

ID  FIRST_NAME  LAST_NAME       LAST_LOGIN_TIME
=============================================
100 Jerry           Seinfeld        2015-07-01
200 George      Costanza        2014-01-01
300 Jerry           Seinfeld        2015-07-31
400 George      Costanza        2015-05-05
500 Elaine          Benes           2013-03-03

I would want my modified query to return

ID  FIRST_NAME  LAST_NAME       LAST_LOGIN_TIME
=============================================
300 Jerry           Seinfeld        2015-07-31
400 George      Costanza        2015-05-05
500 Elaine          Benes           2013-03-03

Any ideas how I would write such a query?

Answer 1

Solution:

    select t1.ID,t1.FIRST_NAME, t1.LAST_NAME, t.max_log
    from Table1 t1
    join (
       select FIRST_NAME,  LAST_NAME, max(LAST_LOGIN_TIME) as max_log
        from Table1
       group by FIRST_NAME,  LAST_NAME
    ) t
    on t.FIRST_NAME = t1.FIRST_NAME
    and t.LAST_NAME = t1.LAST_NAME
    and t.max_log = t1.LAST_LOGIN_TIME
    order by t1.ID

I'm using inner query, because I do not know how IDs are generated. If higher date means higher ID, then we could remove inner query and stay with:

 select max(ID), FIRST_NAME,  LAST_NAME, max(LAST_LOGIN_TIME) as max_log
 from Table1
 group by FIRST_NAME,  LAST_NAME

DEMO HERE

Answer 2

SELECT DISTINCT u.firstname, MAX(lastlogintime) 
FROM tb 
GROUP BY u.firstname 
ORDER BY MAX(lastlogintine) DESC, u.firstname