MySQL select MAX(datetime) not returning max value

Question

Example table:

id   computer  app      version     build    date
---|---------|------|------------|-------|---------
1  |  aaaa1  | app1 |   1.0.0    |   1   | 2013-11-11 09:51:07
2  |  aaaa1  | app2 |   2.0.0    |   2   | 2013-11-12 09:51:07
5  |  xxxx2  | app1 |   1.0.0    |   1   | 2013-11-13 09:51:07
3  |  cccc3  | app2 |   3.1.0    |   1   | 2013-11-14 09:51:07
4  |  xxxx2  | app1 |   1.0.0    |   2   | 2013-11-15 09:51:07
5  |  cccc3  | app2 |   3.1.1    |   3   | 2013-11-16 09:51:07
6  |  xxxx2  | app1 |   1.0.2    |   1   | 2013-11-17 09:51:07
7  |  aaaa1  | app1 |   1.0.2    |   3   | 2013-11-18 09:51:07

Desired output (not exact format or listing order), getting latest install for each app on each computer:

7. aaaa1 - app1 - 1.0.2 - 3 - 2013-11-18 09:51:07
2. aaaa1 - app2 - 2.0.0 - 2 - 2013-11-12 09:51:07
6. xxxx2 - app1 - 1.0.2 - 1 - 2013-11-17 09:51:07
5. cccc3 - app2 - 3.1.1 - 3 - 2013-11-16 09:51:07

My SQL statement:

SELECT 
        id,
        computer, 
        app, 
        version, 
        build, 
        MAX(date) AS installed
    FROM 
        data 
    WHERE 
        placement = 'xxx'
    GROUP BY 
        app, computer
    ;

This gives me:

1. aaaa1 - app1 - 1.0.0 - 1 - 2013-11-11 09:51:07

and not

7. aaaa1 - app1 - 1.0.2 - 3 - 2013-11-18 09:51:07

as I expected.

MAX(date) works if I ONLY select MAX(date) and nothing else. But then I don't get any data to work with (just latest date).

SELECT 
        MAX(date) AS installed

I'm not an SQL ninja so I will soon go bald by scratching my head because of this.

You're not using `GROUP BY` correctly. [See the doc](http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html) — Kermit, Nov 18 '13 at 14:22
my money is on: `WHERE placement = 'xxx'`, but without seeing the values of placement in your sample data, can't be sure... — StevieG, Nov 18 '13 at 14:24
@StevieG its the same for all, as I stated, it works when I only select MAX(date), thus leaving the rest of the query the same, including WHERE. — DannyThunder, Nov 18 '13 at 14:25
@DannyThunder - yes, just realised I missed that last bit. In which case, I agree with FreshPrinceOfSO.. — StevieG, Nov 18 '13 at 14:26
@Kermit - I think you're not correct about that. Assuming the `WHERE placement = 'xxx'` is left off the query (because this column isn't shown in the OP's table listing), and including only `computer`, `app`, and `max(date)` in the `SELECT`, then the `GROUP BY` clause does aggregate within each group exactly as the OP intends. I've just built a MySQL table with exactly this data and run the SQL query as listed by the OP (modified only as I've just indicated), and the result for the `max(date)` ("`installed`") output column is as the OP says he expects. — Dan Nissenbaum, Mar 13 '15 at 17:29
@DanNissenbaum That's because MySQL extends `GROUP BY` functionality. You won't find this in any other platform. It's poor practice and has no guarantee to give you the correct results. — Kermit, Mar 14 '15 at 13:51
Subqueries slow down query execution, check my answer to the similar question to prevent that: http://stackoverflow.com/a/33348557/1630623 — Frane Poljak, Oct 26 '15 at 14:46

Filipe Silva · Accepted Answer · 2015-03-15T18:07:35.533

15

Try like this:

SELECT d.id, d.computer, d.app, d.version, d.build, a.installed
FROM data d
INNER JOIN (
  SELECT computer, app, max(DATE) AS installed
  FROM data
  GROUP BY computer, app
  ) a ON a.computer = d.computer AND a.app = d.app
WHERE placement = 'xxx'

The inner query is getting you the max(date) for each pair of computer and app, then you just join with that to get the rest of the information.

edited Mar 15 '15 at 18:07

answered Nov 18 '13 at 14:23

Filipe Silva

21,189
5
53
68

+1, but why can't you just use `max()` in the outer query and eliminate the join? It seems like it would do the same thing. – Nate Jul 13 '14 at 15:13
1

@Nate. If you did that you would end up with an aggregated result. The inner query is used to get the pair computer-date with the max(date). Then you join it with the same table to get the full details. The option that you suggested is the same as the one that wasn't working in the question. – Filipe Silva Jul 14 '14 at 10:03
Filipe - My previous comment was incorrect (and I've removed it). Considering only at the `max(date)` output column, an inner `SELECT` is not necessary. But - including all fields in the row that contains the maximum date, does require the inner `SELECT`. I've just removed my comment, and I would like to retract my downvote but I'll only be able to if you edit your answer. Apologies! – Dan Nissenbaum Mar 13 '15 at 17:35

score 6 · Answer 2 · answered Nov 18 '13 at 19:55

Try by casting the Datetime field

 SELECT 
            id,
            computer, 
            app, 
            version, 
            build, 
            MAX(cast(date as Datetime)) AS installed
        FROM 
            data 
        WHERE 
            placement = 'xxx'
        GROUP BY 
           app, computer, id, version, build
        ;

score 0 · Answer 3 · answered Nov 18 '13 at 14:22

0

max - is an aggregate function try to add all columns from select statement to GROUP BY:

GROUP BY 
    app, computer, id, version, build.

answered Nov 18 '13 at 14:22

edem

3,222
3
19
45

score 0 · Answer 4 · answered Nov 18 '13 at 14:28

It might be because you store your date as String, and comparing string act differently than comparing integer. You should store your date in unix Timestamp format, and they will be much easier to compare. But will need an extra effort to be displayed as normal English date.

score 0 · Answer 5 · answered Jun 04 '15 at 10:29

0

Whats wrong with:

SELECT 
    id,
    computer, 
    app, 
    version, 
    build, 
    `date` AS installed
FROM 
    data 
WHERE 
    placement = 'xxx'
ORDER BY installed DESC
GROUP BY app;

answered Jun 04 '15 at 10:29

Gravy

12,264
26
124
193

score 0 · Answer 6 · answered Jan 16 '19 at 11:43

MAX did not work for me, what worked was additional subquery where I preordered table by date:

SELECT d.id, d.computer, d.app, d.version, d.build, a.installed
FROM data d
INNER JOIN (
  SELECT computer, app, date as installed
  FROM (
    SELECT computer, app, date
    FROM data
    ORDER BY date desc
  ) as t
  GROUP BY computer, app
  ) a ON a.computer = d.computer AND a.app = d.app
WHERE placement = 'xxx'

MySQL select MAX(datetime) not returning max value

6 Answers6

Linked