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.
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
)
Workaround but working solution
Only if ID is autoincrement, you can search for the maximum id instead of the max date. So, by the ID you can find all others fields.
select *
from table
where id IN (
select max(id)
from table
group by #MY_FIELD#
)
SELECT report_id, computer_id, date_entered
FROM reports
WHERE date_entered = (
SELECT date_entered
FROM reports
ORDER date_entered
DESC LIMIT 1
)
It works great for me
SELECT report_id,computer_id,MAX(date_entered) FROM reports GROUP BY computer_id
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!
select report_id, computer_id, date_entered
into #latest_date
from reports a
where exists(select 'x' from reports
where a.report_id = report_id
group by report_id having max(date_entered) = a.date_entered)
select * from #latest_leave where computer_id = ##
I use this solution having max(date_entered)
and it works very well
SELECT
report_id,
computer_id,
date_entered
FROM reports
GROUP BY computer_id having max(date_entered)
This would work perfectely, if you are using current timestamp
SELECT * FROM reports WHERE date_entered = (SELECT max(date_entered) FROM REPORTS)
This would also work, if you are not using current timestamp but you are using date and time column seperately
SELECT * FROM reports WHERE date_entered = (SELECT max(date_entered) FROM REPORTS) ORDER BY time DESC LIMIT 1
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
Works perfect for me:
(SELECT content FROM tblopportunitycomments WHERE opportunityid = 1 ORDER BY dateadded DESC LIMIT 1);
Did this on a blog engine to get the latest blog. I adapted it to your table structure.
SELECT * FROM reports WHERE date_entered = (SELECT max(date_entered) FROM REPORTS)
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.
Source: Stackoverflow.com