php mysql 只获取到第一条数据_,php mysql Group By获取最新记录，而不是第一条记录...

The Table:

(`post_id`, `forum_id`, `topic_id`, `post_time`)

(79, 8, 4, '2012-11-19 06:58:08');

(80, 3, 3, '2012-11-19 06:58:42'),

(81, 9, 9, '2012-11-19 06:59:04'),

(82, 11, 6, '2012-11-19 16:05:39'),

(83, 9, 9, '2012-11-19 16:07:46'),

(84, 9, 11, '2012-11-19 16:09:33'),

The Query:

SELECT post_id, forum_id, topic_id FROM posts

GROUP BY topic_id

ORDER BY post_time DESC

LIMIT 5

The Results:

[0] => [post_id] => 84 [forum_id] => 9 [topic_id] => 11

[1] => [post_id] => 82 [forum_id] => 11 [topic_id] => 6

[2] => [post_id] => 81 [forum_id] => 9 [topic_id] => 9

[3] => [post_id] => 80 [forum_id] => 3 [topic_id] => 3

[4] => [post_id] => 79 [forum_id] => 8 [topic_id] => 4

The Problem:

How to rewrite the query so that it returns post_id -> 83 instead of post_id -> 81 ?

They both have the same forum and topic ids, but post_id -> 81 has an older date than post_id -> 83.

But it seems that Group By gets the 'first' record and not the 'newest' one.

I tried changing the query to

SELECT post_id, forum_id, topic_id, MAX(post_time)

but that returns both post_id 81 and 83

解决方案

If you select attributes that are not used in the group clause, and are not aggregates, the result is unspecified. I.e you don't know which rows the other attributes are selected from. (The sql standard does not allow such queries, but MySQL is more relaxed).

The query should then be written e.g. as

SELECT post_id, forum_id, topic_id

FROM posts p

WHERE post_time =

(SELECT max(post_time) FROM posts p2

WHERE p2.topic_id = p.topic_id

AND p2.forum_id = p.forum_id)

GROUP BY forum_id, topic_id, post_id

ORDER BY post_time DESC

LIMIT 5;

or

SELECT post_id, forum_id, topic_id FROM posts

NATURAL JOIN

(SELECT forum_id, topic_id, max(post_time) AS post_time

FROM posts

GROUP BY forum_id, topic_id) p

ORDER BY post_time

LIMIT 5;