mysql select max row by one field when using group by

Question

mysql> select * from test;
+----+--------+------+------------+---------+
| id | title  | tag  | date       | content |
+----+--------+------+------------+---------+
| 38 | title1 | xx   | 1514521364 | 1       |
| 39 | title2 | xx   | 1514521365 | 1       |
| 40 | title3 | xx   | 1514521366 | 1       |
| 41 | title4 | xx   | 1514521367 | 2       |
| 42 | title5 | xx   | 1514521368 | 2       |
+----+--------+------+------------+---------+
5 rows in set (0.01 sec)

this is my test table. l need get the result like follow:

+----+--------+------+------------+---------+
| id | title  | tag  | date       | content |
+----+--------+------+------------+---------+
| 40 | title3 | xx   | 1514521366 | 1       |
| 42 | title5 | xx   | 1514521368 | 2       |
+----+--------+------+------------+---------+

l group by content field, and need select row by max(date), because l need the id field correct.

sql may like select id, max(date), content from test group by content;

l tried some way, but failed. l really a rookie in mysql.

Answer 1

Try This:-

SELECT *
FROM test
WHERE date IN (SELECT MAX(date)
               FROM test GROUP BY content);

Answer 2

This query will do what you want. It JOINs the table to a list of the maximum dates for each content based on the content and date matching, thus giving you the correct values of id, title and tag:

SELECT t.*
FROM test t
JOIN (SELECT content, MAX(date) AS maxdate
      FROM test
      GROUP BY content) t1
ON t1.content = t.content AND t1.maxdate = t.date

Output:

id  title   tag     date        content
40  title3  xx      1514521366  1
42  title5  xx      1514521368  2

SQLFiddle Demo