how to group by column name and ensure the query retrieves the last update

Question

I am currently working with MySQL creating a view that would return the following:

NAME | EMAIL | LAST_SEEN

abby | a@l.d | 2015-10-31 14:36:26
abby | a@l.d | 2015-11-28 13:30:37

I then apply the GROUP BY name to the select query and it returns the following

 NAME | EMAIL | LAST_SEEN

 abby | a@l.d | 2015-10-31 14:36:26

I want to know how can I fix this query so that it returns the following:

NAME | EMAIL | LAST_SEEN

abby | a@l.d | 2015-11-28 13:30:37

the actual code is as follows:

CREATE VIEW v_user_last_seen
AS
SELECT concat_ws(' ', u.first_name, u.middle_name, u.last_name) AS user_name
    ,c.email
    ,l.in_when AS last_seen
FROM user AS u
INNER JOIN check_here_first AS c ON c.email = u.email
INNER JOIN log AS l ON l.u_id = c.username
GROUP BY user_name
ORDER BY user_name ASC

http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html — Abhik Chakraborty, Nov 29 '15 at 15:56
Possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) — Sean, Nov 29 '15 at 16:00

ScaisEdge · Accepted Answer · 2015-11-29T16:02:59.110

1

simply use max(last_seen)

 select name, email, max(last_seen) 
 from  yourtable,
 group by name, email;

edited Nov 29 '15 at 16:02

answered Nov 29 '15 at 16:01

ScaisEdge

131,976
10
91
107

Selfish · Answer 2 · 2015-11-30T11:09:01.507

Try to sort your table before grouping, this should do for simple cases such as this one:

SELECT * 
FROM ( SELECT * 
       FROM `tablename` 
       ORDER BY `LAST_SEEN` DESC 
     ) temp
GROUP BY `name`

If this fails, you can do something like:

SELECT tmp1.*
FROM `tablename` tmp1
LEFT JOIN `tablename` tmp2
    ON (tmp1.`name` = tmp2.`name` 
        AND tmp1.`LAST_SEEN` < tmp2.`LAST_SEEN`)
WHERE tmp2.`name` IS NULL

The idea here is matching the table's rows with itself, where the matched values have a higher values. for that ones that don't match, we will get a null, as we use LEFT JOIN. These should be the highest in the group.

how to group by column name and ensure the query retrieves the last update

2 Answers2