mysql查询id最大 max,MySQL SELECT行的ID，其中几列的MAX条目最大

I have this table mytable:

+----+--------------------------------------+

| id | date1 | date2 | date3 |

+----+--------------------------------------+

| 1 | 2014-01-08 | NULL | NULL |

| 2 | 2014-05-09 | NULL | NULL |

| 3 | 2014-06-13 | NULL | NULL |

| 4 | NULL | 2014-03-24 | NULL |

| 2 | NULL | NULL | 2014-08-15 |

| 4 | 2014-01-01 | NULL | NULL |

| 1 | 2014-02-15 | NULL | NULL |

| 3 | NULL | 2014-12-06 | 2014-10-12 |

| 4 | 2014-08-06 | NULL | NULL |

| 2 | 2014-05-22 | NULL | NULL |

+----+--------------------------------------+

Now I try to have one SELECT with the following result:

id max_date1 max_date2 max_date3

---------------------------------------------

| 3 | 2014-08-06 | 2014-12-06 | 2014-10-12 |

That means the MAX of each date column and the ID from the row in which the GREATEST of the MAX results is.

The Query, that brought me nearly to the solution looks like this:

SELECT

id, max(date1), max(date2), max(date3), GREATEST(

IFNULL(max(date1), 0),

IFNULL(max(date2), 0),

IFNULL(max(date3), 0)) AS maxdate

FROM table1

But the id i get, is not the expected one. How can I find out which column has the maxdate so I can find out the appendant id?

解决方案

a way to do it would be to store your date in a user-defined-variable. then you can use it to pull out the id for the largest date

SET @A := (SELECT GREATEST(

IFNULL(max(date1), 0),

IFNULL(max(date2), 0),

IFNULL(max(date3), 0)

) FROM table1

);

-- here i JOIN a select that pulls out the correct id

SELECT t.joinid, max(`date1`), max(`date2`), max(`date3`)

FROM table1

JOIN

( SELECT id as joinid

FROM table1

WHERE @A IN -- WHERE my MAX date is in

(

SELECT date1 -- here the UNION is just putting all of the dates into one column to compare one date with

UNION ALL SELECT date2

UNION ALL SELECT date3

)

) t -- every table must have an alias