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.

post_time is int(11). I think I need a 2nd query inside of the query to get the latest post_time of the poster_id but I dont know how to connect this. — labu77, Jan 17 '23 at 13:30
Show the last post_time instead of the first post_time in each row for every single poster_id. Do you understand? Thank you very much for your time. — labu77, Jan 17 '23 at 13:37
Does this answer your question? [Retrieving the last record in each group - MySQL](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql) — nbk, Jan 17 '23 at 14:14

Jens · Accepted Answer · 2023-01-19T09:37:13.143

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

edited Jan 19 '23 at 09:37

answered Jan 17 '23 at 13:40

Jens

67,715
15
98
113

Thank you very much for your time. I will test this in the next few minutes. I thought so, that I need a second query but I dont know how...brb. – labu77 Jan 17 '23 at 13:42
@Jens as you already pointed out *Using columns in Select which are not in group by is in most db's forbiden, becuase it is not defined which values are shown* you shouldn't give an answer which will fail with *this is incompatible with sql_mode=only_full_group_by*. https://dbfiddle.uk/3lZazU49 – Ergest Basha Jan 17 '23 at 13:46
1

@ErgestBasha Thanks for the hint. Have overseen it and changed my answer now – Jens Jan 17 '23 at 13:53
I tried this query but our SQL was gone because of a loop, so I had to restart the hole server. I would love to spend you guys a coffee or donation via paypal. How is it possible to contact you, to get this sorted? – labu77 Jan 17 '23 at 13:57
@Jens I accpeted your anwser now, because this query works. You forgot just one think in the query and this was the topic_id. Could you please add this before the group by? And then I have another question. Is it possible to count how many group by´s was done inside the query? Maybe something like COUNT(poster_id) as POSTSDONE? – labu77 Jan 19 '23 at 08:46
@labu77 I have done it – Jens Jan 19 '23 at 09:37
@Jens Thank you. Like a charm ;) - Could you please say something to my last question? Is it possible to count the poster_id entrys in the posts table, before the poster_id gets grouped by? Das wäre wirklich klasse, wenn ich diese Information noch mit anzeigen könnte. Vielen Dank für diese letzte Info Jens. – labu77 Jan 19 '23 at 10:05
@labu77 If you work with CTE than you can do it – Jens Jan 19 '23 at 10:22
@Jens I see, CTE means Common Table Expression, I dont know how to do this. I just want to use your query. Is it possible to implement a counting in it? – labu77 Jan 19 '23 at 10:26
somthing like `SELECT p.poster_id, p.post_time, p.post_id, p.post_time, u.user_id, u.username, numberof FROM posts as p INNER JOIN users as u ON u.user_id = p.poster_id INNER JOIN (select poster_id, count(*) as numberOf from posts as x group by poster_id) as y on y.poster_id=p.poster_id where (p.poster_id, p.post_time )in(select poster_id, max(post_time) from posts group by poster_id ,p.post_time )` – Jens Jan 19 '23 at 10:35
Awesome Jens, works like a charm. You only missed the topic_id in the inner join. I added it and now I have the number. Thank you very much. – labu77 Jan 19 '23 at 11:02

lemon · Answer 2 · 2023-01-17T14:05:33.377

1

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.

edited Jan 17 '23 at 14:05

answered Jan 17 '23 at 13:50

lemon

14,875
6
18
38

Thank you very much for your time. I use mariaDB. It this also possible? – labu77 Jan 17 '23 at 14:00
Yes, this solution would work in MariaDB too. Make sure to avoid tagging the wrong DBMS in your post. – lemon Jan 17 '23 at 14:01
Thank you. You used topic_id6 but the forum has many topics, not only topic_id 6. Is it possible to write this different, to match all topics and not only topic_id 6? I know this WHERE topic_id = 6 I can change but in your query the whole names has 6 in it. – labu77 Jan 17 '23 at 14:04
Check the updated query, make sure to point out in your post description that you need this query to be generalized on topic_id values. – lemon Jan 17 '23 at 14:06
Check also the updated fiddle link. – lemon Jan 17 '23 at 14:08

GROUP BY with ORDER BY / last post_time

2 Answers2