Getting wrong values for other columns when I select MAX(updated_date)
You either need a GROUP BY clause or a more complex query.
SELECT field1, MAX(updated_date) FROM mytable GROUP BY field1
For the sample data, this will return 3 rows.
More likely, you want:
SELECT t1.field1, t3.max_date FROM mytable AS t1 JOIN (SELECT MAX(t2.updated_date) AS max_date FROM mytable AS t2 ) AS t3 ON t1.updated_date = t3.max_date;
For the sample data, this will return 1 row:
ta3 2012-03-11 11:05:56
Of the major DBMS, only MySQL allows you to omit the GROUP BY clause when you have a mixture of aggregates and non-aggregate columns in the select-list. The SQL standard requires the GROUP BY clause and you must list all non-aggregate columns in it. Sometimes, in MySQL, omitting the GROUP BY clause produces the answer you want; as often as not, though, it manages to give an unexpected answer.
Use ORDER BY and LIMIT.
Then it's as simple as:
SELECT field1, updated_date FROM mytableORDER BY updated_date DESC LIMIT 1;
If the query is needed a lot you can try this alternative:
SELECT t1.field1, t1.updated_date FROM mytable t1 LEFT JOIN mytable t2 AND t2.updated_date > t1.updated_date WHERE t2.field1 IS NULL;
Short explanation:
For each row, give me any rows with a more-recent updated_date
.
But (WHERE clause) take only the row with no more-recent updated_date
.
The technique is sometimes called a self-exclusion join.
This is the intermediate result (without WHERE clause, and adding t2.*
to SELECT list):
ta1 2012-03-11 11:05:15 ta2 2012-03-11 11:05:32ta1 2012-03-11 11:05:15 ta3 2012-03-11 11:05:56ta2 2012-03-11 11:05:32 ta3 2012-03-11 11:05:56ta3 2012-03-11 11:05:56 null null