Latest data group-by without a subquery

Question

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

TABLE POSTS [ ID, USER_ID, TOPIC_ID, TIME ]

I want to get only the last post by 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

Is this correct? Is there a way to do this without the subquery?

Is there an option to get this data with spring-data queries, or this is a JDBC/stored procedure only thing?

Answer 1

You can get the latest post for each user without using a subquery by 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