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":

Table "POSTS"

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.

CodePudding user response：

The problem with your query is that all your non-aggregated fields must be present inside the GROUP BY clause. As long as this condition is not met, you either have an error fired by your DBMS, or expect some subtle output mistakes. Additionally your query would fire a syntax error due to the comma after the last selected field in the SELECT clause.

If you're using MySQL 8.0, you can use the ROW_NUMBER window function to select your last post time for each poster (rownum = 1, partitioned by poster, ordered by post_time desc), then join back to your users table to get information.

WITH topic_id6 AS (
    SELECT poster_id, post_time, post_id, topic_id,
           ROW_NUMBER() OVER(PARTITION BY topic_id, poster_id ORDER BY post_time DESC) AS rn 
    FROM POSTS
)
SELECT id6.poster_id, id6.post_time, id6.post_id, id6.topic_id, u.*
FROM       topic_id6 id6 
INNER JOIN USERS     u
        ON id6.poster_id = u.user_id
       AND id6.rn = 1

Check the demo here.

CodePudding user response：

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.poster_id, p.post_time )in(select poster_id, max(post_time) from posts group by poster_id )

Demo