mysql日期最大值,MySQL选择MAX（日期时间）不返回最大值

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.

解决方案

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.