How to SELECT by MAX(date)?
Solution 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
)
Solution 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.
Solution 3
This is a very old question but I came here due to the same issue, so I am leaving this here to help any others.
I was trying to optimize the query because it was taking over 5 minutes to query the DB due to the amount of data. My query was similar to the accepted answer's query. Pablo's comment pushed me in the right direction and my 5 minute query became 0.016 seconds. So to help any others that are having very long query times try using an uncorrelated subquery.
The example for the OP would be:
SELECT
a.report_id,
a.computer_id,
a.date_entered
FROM reports AS a
JOIN (
SELECT report_id, computer_id, MAX(date_entered) as max_date_entered
FROM reports
GROUP BY report_id, computer_id
) as b
WHERE a.report_id = b.report_id
AND a.computer_id = b.computer_id
AND a.date_entered = b.max_date_entered
Thank you Pablo for the comment. You saved me big time!
Solution 4
Accordig to this: https://bugs.mysql.com/bug.php?id=54784 casting as char should do the trick:
SELECT report_id, computer_id, MAX(CAST(date_entered AS CHAR))
FROM reports
GROUP BY report_id, computer_id
Solution 5
It works great for me
SELECT
report_id,computer_id,MAX(date_entered)
FROM
reports
GROUP BY
computer_id
poetter747
Updated on January 06, 2022Comments
-
poetter747 over 2 years
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 thereport_id
percomputer_id
from latest entereddate_entered
, and I have no clue how to do that.