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.

score 63 · Accepted Answer · answered Oct 20 '11 at 13:12

63

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
)

answered Oct 20 '11 at 13:12

bhamby

15,112
1
45
66

2

Almost. I left out "a.report_id = b.report_id" and that did the trick. Thank you – poetter747 Oct 20 '11 at 13:23
16

This is a little inefficient because you are generating too many subqueries. Instead, try to use a uncorrelated subquery. https://dev.mysql.com/doc/refman/5.6/en/example-maximum-column-group-row.html – Pablo Jul 20 '17 at 20:01
4

Pablo is correct. The filtering can be done earlier by joining a subquery, thus saving performance. – twicejr Dec 15 '17 at 09:45

score 17 · Answer 2 · edited Oct 20 '11 at 12:53

17

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.

edited Oct 20 '11 at 12:53

Johan

74,508
24
191
319

answered Oct 20 '11 at 12:44

Tyler Ferraro

3,753
1
21
28

7

When id do this, I get the wrong report_id. I checked the statement with a WHERE computer_id = 30. The result was the first report_id of all found report_ids in combination with the latest date_entered – poetter747 Oct 20 '11 at 12:55

score 5 · Answer 3 · answered Oct 26 '15 at 14:43

5

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

answered Oct 26 '15 at 14:43

Frane Poljak

2,315
23
25

5

This will get you the max date, but not necessarily the other values from the same row. – Vincent Jan 04 '18 at 02:22

Jeremy · Answer 4 · 2018-08-24T01:43:58.053

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!

score 2 · Answer 5 · answered Oct 25 '20 at 09:48

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

score 1 · Answer 6 · answered Feb 07 '19 at 10:31

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#
              )

score 1 · Answer 7 · answered May 14 '19 at 07:35

1

Works perfect for me:

(SELECT content FROM tblopportunitycomments WHERE opportunityid = 1 ORDER BY dateadded DESC LIMIT 1);

answered May 14 '19 at 07:35

xayer

413
4
11

score 1 · Answer 8 · edited Nov 24 '20 at 09:53

1

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 = ##

edited Nov 24 '20 at 09:53

Elletlar

3,136
7
32
38

answered Nov 24 '20 at 07:43

siraj k

19
4

1

This answer is missing its educational explanation. – mickmackusa Mar 27 '22 at 01:34

score 0 · Answer 9 · answered Jul 26 '23 at 18:13

If you sort the table by date_entered descending, grouping collects the correct rows:

SELECT * FROM (
  SELECT report_id, computer_id, date_entered ORDER BY date_entered DESC
) sorted 
GROUP BY computer_id

This fully pages in the indices (assuming you indexed all three columns - otherwise full table scan), but if your indices are on HDD that will often be the fastest solution.

score -1 · Answer 10 · edited Dec 15 '17 at 09:34

-1

SELECT report_id, computer_id, date_entered
FROM reports
WHERE date_entered = (
    SELECT date_entered 
    FROM reports 
    ORDER date_entered 
    DESC LIMIT 1
)

edited Dec 15 '17 at 09:34

answered Dec 15 '17 at 09:15

Daniel Gray

1

2

Thank you for this code snippet, which might provide some limited, immediate help. A proper explanation [would greatly improve](//meta.stackexchange.com/q/114762) its long-term value by showing *why* this is a good solution to the problem, and would make it more useful to future readers with other, similar questions. Please [edit] your answer to add some explanation, including the assumptions you've made. – Toby Speight Dec 15 '17 at 10:29

score -1 · Answer 11 · answered Jun 06 '19 at 02:41

-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)

answered Jun 06 '19 at 02:41

sixstring

262
4
10

score -1 · Answer 12 · edited Jan 05 '22 at 23:28

-1

It works great for me

SELECT
    report_id,computer_id,MAX(date_entered)
FROM
    reports
GROUP BY
    computer_id

edited Jan 05 '22 at 23:28

Giacomo1968

25,759
11
71
103

answered Sep 26 '19 at 08:02

east

15
2

2

I don't know why this got upvoted. It is wrong. There can be many report_id per computer_id. You don't tell the DBMS which you want. MySQL should throw an error. It does so, provided you `SET sql_mode = 'ONLY_FULL_GROUP_BY';` as you should. If you don't, MySQ will silently aply `ANY_VALUE(report_id)`, so the returned report_id can come from any row in the group. It gets arbitrarily picked, and the query is not deterministic. – Thorsten Kettner Mar 16 '22 at 17:30

score -2 · Answer 13 · edited Sep 18 '19 at 14:59

-2

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)

edited Sep 18 '19 at 14:59

Meloman

3,558
3
41
51

answered May 10 '19 at 17:06

LuyRamone

35
2

1

This didn't work for me, still getting the first value, not the max – Pedro Joaquín Jun 20 '19 at 13:32

How to SELECT by MAX(date)?

13 Answers13

Linked