How to SELECT by MAX(date)?

Question

This is the table structure:

CREATE TABLE `reports` (   `report_id` int(11) NOT NULL auto_increment,   `computer_id` int(11) NOT NULL default '0',   `date_entered` datetime NOT NULL default '1970-01-01 00:00:00',   `total_seconds` int(11) NOT NULL default '0',   `iphone_id` int(11) default '0',   PRIMARY KEY  (`report_id`),   KEY `computer_id` (`computer_id`),   KEY `iphone_id` (`iphone_id`) ) ENGINE=MyISAM AUTO_INCREMENT=120990 DEFAULT CHARSET=latin1 

I need a SELECT statement that will list the report_id per computer_id from latest entered date_entered, and I have no clue how to do that.

Answer 1

This should do it:

SELECT report_id, computer_id, date_entered FROM reports AS a WHERE date_entered = (     SELECT MAX(date_entered)     FROM reports AS b     WHERE a.report_id = b.report_id       AND a.computer_id = b.computer_id ) 

Answer 2

Are you only wanting it to show the last date_entered, or to order by starting with the last_date entered?

SELECT report_id, computer_id, date_entered FROM reports GROUP BY computer_id ORDER BY date_entered DESC -- LIMIT 1 -- uncomment to only show the last date.