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

User · Answer

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!

User · Answer

It works great for me  SELECT report id computer id MAX date entered  FROM reports GROUP BY computer id

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Works perfect for me    SELECT content FROM tblopportunitycomments WHERE opportunityid   1 ORDER BY dateadded DESC LIMIT 1

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT report id  computer id  date entered FROM reports WHERE date entered         SELECT date entered      FROM reports      ORDER date entered      DESC LIMIT 1

User · Answer

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

[mysql] How to SELECT by MAX(date)?

Examples related to mysql

Examples related to sql

Examples related to select

Examples related to max