Max function issue with the values from other columns

Question

I have the following MySQL query :

Select match_static_id, comments as last_comment, max(timestamp)
from comments as c 
group by match_static_id;

I have table for comment on matches and i want to have the latest comment of each match. so i use the max(timestamp) and group by (match_static_id) for that but my problem that i get the max timestamp grouped by match_static_id but i get other comment(not the comment of the max timestamp) Is my query ordered in wrong way?

Answer 1

I am not an expert in mysql but I can sense the issue. It could be because the comments in not part of group by, it would return all the rows that match the match_static_id. I would suggest rewriting something like:

select match_static_id, (select Comment from comments c where c.timestamp =max(a.timestamp)) as last_comment, max(timestamp) from comments group by match_staic_id

or

select c.match_static_id, c.comments as last_comment, c.timestamp from comments c inner join (Select max(timestamp) as ts from comments group by match_static_id) temp c.timestamp = temp.ts

Answer 2

That will solve:

SELECT match_static_id, comments AS last_comment, login_time2
  FROM  comments c
WHERE timestamp=
    ( SELECT MAX(timestamp) AS login_time2
      FROM comments WHERE match_static_id=c.match_static_id)
 GROUP BY match_static_id;