Last data group without subquery

Question

This is a fairly common case, but I'm not sure if something is wrong.

TABLE POSTS [ ID, USER_ID, TOPIC_ID, TIME ] 

I want to receive only the last message from each user on topic_id '1'

 SELECT p.* FROM posts p, ( SELECT user_id, max(time) mtime FROM posts WHERE topic_id = 1 GROUP BY user_id ) pm WHERE p.user_id = pm.user_id AND p.time = pm.mtime 

It is right? Is there any way to do this without a subquery?

Is it possible to get this data using spring-data queries, or is it just a JDBC / stored procedure?

Answer 1

You can get the last message for each user without using a subquery using join:

 SELECT p.* FROM posts p LEFT OUTER JOIN posts t ON(p.user_id = t.user_id and p.time < t.time and t.topic_id = 1) WHERE p.topic_id = 1 and t.topic_id is null 

Answer 2

Not sure how to avoid sub-query here, but another way to do this would be

 SELECT p.* FROM posts p WHERE p.time = (SELECT Max(time) mtime FROM posts pm WHERE pm.topic_id = 1 AND p.user_id = pm.user_id) 

Window Row_number function will be very useful in such cases, unfortunately Mysql does not support

Answer 3

To do this without a subquery, use HAVING .

 SELECT user_id, topic_id, time FROM posts WHERE topic_id = 1 GROUP BY user_id HAVING time = max(time) 

Answer 4

Another solution that avoids the sub-query (but again, may not be so fast) uses substring_index, sorted by decreasing time for each field from which you want to get the last one, and then using SUBSTRING_INDEX to just grab the first record returned by SUBSTRING_INDEX.

 SELECT user_id, SUBSTRING_INDEX(GROUP_CONCAT(`id` ORDER BY `time` DESC), ',', 1), SUBSTRING_INDEX(GROUP_CONCAT(`topic_id` ORDER BY `time` DESC), ',', 1), SUBSTRING_INDEX(GROUP_CONCAT(`time` ORDER BY `time` DESC), ',', 1) FROM posts WHERE topic_id = 1 GROUP BY user_id