0

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?

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Basel
  • 1,305
  • 7
  • 25
  • 34
  • Please provide some input output pattern. it will be very much helpful if you can provide demo on [SQLFiddel.com](http://sqlfiddle.com/) – Romesh Sep 06 '13 at 09:15

2 Answers2

0

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
Krishna Sarma
  • 1,852
  • 2
  • 29
  • 52
0

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;
jaczes
  • 1,366
  • 2
  • 8
  • 16
  • This query is giving the all the comments i just want to get the comment of the match that have the max timestamp – Basel Sep 06 '13 at 10:04
  • there is `i want to have the latest comment of each match` - there is `EACH` - there is simple update in naswer. – jaczes Sep 06 '13 at 10:19
  • ok my friend let me make my self clear there is a misunderstanding . I want the last comment for EACH match that is right but in your first query I got all the comments of a match with the same timestamp for all of them (the max timestamp). I just want one comment of each match which is the one with the max timestamp . I hope i make myself clear now – Basel Sep 06 '13 at 10:28
  • oh, i get it. see now. – jaczes Sep 06 '13 at 11:41