I'm trying to find maxumum value of column for each member of a distinct group.

Sample Table:
================================================== ===
| id | rid | report_date | report_date_formatted |
| 1 | 1 | 1014267600 | 2002-02-21 |
| 128 | 1 | 1202619600 | 2008-02-10 |
| 158 | 2 | 1016082000 | 2002-03-14 |
| 392 | 2 | 1203138000 | 2008-02-16 |

The following SQL statement works fine:

SELECT rid, MAX(report_date) AS maxreportdate FROM report_posts GROUP BY rid

Output:
=======================
| rid | report_date |
| 1 | 1202619600 |
| 2 | 1203138000 |


Here is the problem... If I try to add another column to the query, the extra column does not match the results. This is NOT working:

SELECT rid, report_date_formatted, MAX(report_date) AS maxreportdate FROM report_posts GROUP BY rid

Erroneous Output:
====================
| rid | report_date | report_date_formatted |
| 1 | 1202619600 | 2003-08-07 |
| 2 | 1203138000 | 2007-04-10 |

The first two columns (rid & report_date) are correct and as expected, BUT the final additional column is INCORRECT and does not correspond to the selected row.

it beats me why you even store the formatted date -- don't

if this works --

SELECT rid, MAX(report_date) AS maxreportdate FROM report_posts GROUP BY rid

then so will this --

I agree with r937, it doesn't make much sense to store the same value twice in the same row, just formatted differently. That sounds like a job for the presentation layer.

Having said that, it doesn't matter what value you have in the third column, your query won't work because you are using an aggregate function and the third column isn't, and can't be, in the group by clause. Instead, you need to use a correlated subquery:

SELECT RID, REPORT_DATE, REPORT_DATE_FORMATTED
FROM test.sample AS s
WHERE (REPORT_DATE =
(SELECT MAX(REPORT_DATE)
FROM test.sample
WHERE (s.RID = RID)))

In my tests, this returned the expected result set.

Hope this helps.