Getting last post user id (MySQL)

Question

I have a little problem - i would can get with this query topics posts count and last post id, but i can't figure out, how to get right user id. I get first post (lowest id) user id but i want lastest post... I have tried adding "ORDER BY id DESC" but this will not help. Any ideas how to do it?

SELECT 
    COUNT(`id`) AS `count`, 
    MAX(`id`) AS `last_post_id`, 
    `topic_id`, 
    `user_id` 
FROM `forum_posts` 
WHERE `topic_id` IN (326, 207, 251) 
GROUP BY `topic_id` 

Answer 1

In your query userid returns the userid of any userid in that group, which will typically be the first one that appears in whatever index the optimizer happened to pick, but it does not have to be.

Self-join to fetch the user_id from the row that has the id you are interested in:

SELECT T1.`count`, T1.last_post_id, T1.topic_id, T2.user_id
FROM (
    SELECT 
        COUNT(id) AS `count`,
        MAX(id) AS last_post_id,
        topic_id, 
    FROM forum_posts
    WHERE topic_id IN (326, 207, 251) 
    GROUP BY topic_id
) T1
JOIN forum_posts T2
ON T1.last_post_id = T2.id

Answer 2

You should be using ORDER BY on a timestamp that you store in the database when the post is created. This will ensure that you are sorting the posts by time, and not by ID, since ID can be messed up if you delete / add posts.