GROUP BY with ORDER BY / last post_time

Question

I have a list of writers in a topic. The List shows the writers name a the last post time inside this topic.

A member can also write more frequently in the topic. Therefore a "group by" was set on the poster_id.

The whole list should be sorted according to the write date. The newest comes first. So I placed maxtime DESC.

Problem:

The list output is working very well but the date of a writer's last post is not the last post_time but always the first post_time.

Table "USERS":

user_id	username
1	Marc
2	Paul
3	Sofie
4	Julia

Table "POSTS"

post_id	topic_id	poster_id	post_time
4565	6	1	999092051
4567	6	4	999094056
4333	6	2	999098058
7644	6	1	999090055

This is my query:

SELECT 
    p.poster_id, 
    p.post_time, 
    p.post_id,   
    Max(p.post_time) AS maxtime, 
    u.user_id, 
    u.username,
FROM POSTS as p 
INNER JOIN USERS as u ON u.user_id = p.poster_id 
WHERE p.topic_id = 6 
GROUP BY p.poster_id 
ORDER BY maxtime DESC

How is it possible to display the last post_time of the poster_id instead the first one.

Answer 1

Using columns in Select which are not in group by or in an aggregation function is in most db's forbiden, becuase it is not defined which values are shown.

You can use a subquery with group by and having

SELECT 
    p.poster_id, 
    p.post_time, 
    p.post_id,   
    p.topic_id,
    u.user_id, 
    u.username                
FROM posts as p 
INNER JOIN users as u ON u.user_id = p.poster_id 
where p.topic_id = 6 AND (p.poster_id, p.post_time )in(select poster_id, max(post_time) from posts group by poster_id )

Demo