MySQL: Undesired result with max function on a timestamp

Question

I use a Mantis Bug Database (that uses MySQL) and I want to query which bugs had a change in their severity within the last 2 weeks, however only the last severity change of the bug should be indicated.

The problem is, that I get multiple entries per bugID (which is the primary key), which is not my desired result since I want to have only the latest change per bug. This means that somehow I am using the max function and the group by clause wrongfully.

Here you can see my query:

SELECT `bug_id`,
    max(date_format(from_unixtime(`mantis_bug_history_table`.`date_modified`),'%Y-%m-%d %h:%i:%s')) AS `Severity_changed`,
    `mantis_bug_history_table`.`old_value`,
    `mantis_bug_history_table`.`new_value`
    from `prepared_bug_list` 
    join `mantis_bug_history_table` on `prepared_bug_list`.`bug_id` = `mantis_bug_history_table`.`bug_id` 
    where (`mantis_bug_history_table`.`field_name` like 'severity') 
    group by `bug_id`,`old_value`,`.`new_value`
    having (`Severity_modified` >= (now() - interval 2 week))
    order by bug_id` ASC

For the bug with the id 8 for example I get three entries with this query. The bug with the id 8 had indeed three severity changes within the last 2 weeks but I only want to get the latest severity change.

What could be the problem with my query?

Answer 1

I finally have a solution! I friend of mine helped me and one part of the solution was to include the Primary key of the mantis bug history table, which is not the bug_id, but the column id, which is a consecutive number. Another part of the solution was the subquery in the where clause:

    select `prepared_bug_list`.`bug_id` AS `bug_id`,
`mantis_bug_history_table`.`old_value` AS `old_value`,
`mantis_bug_history_table`.`new_value` AS `new_value`,
`mantis_bug_history_table`.`type` AS `type`,
date_format(from_unixtime(`mantis_bug_history_table`.`date_modified`),'%Y-%m-%d %H:%i:%s') AS `date_modified`
 FROM `prepared_bug_list`
 JOIN mantis_import.mantis_bug_history_table
 ON `prepared_bug_list`.`bug_id` = mantis_bug_history_table.bug_id
 where (mantis_bug_history_table.id = -- id = that is the id of every history entry, not confuse with bug_id
    (select `mantis_bug_history_table`.`id` from `mantis_bug_history_table` 
     where ((`mantis_bug_history_table`.`field_name` = 'severity') 
     and (`mantis_bug_history_table`.`bug_id` = `prepared_bug_list`.`bug_id`))
    order by `mantis_bug_history_table`.`date_modified` desc limit 1)
and `date_modified` > unix_timestamp() - 14*24*3600 ) 
order by `prepared_bug_list`.`bug_id`,`mantis_bug_history_table`.`date_modified` desc