I have a table containing topic_id, post_id, post_time, and poster_id. How do I get a list of the last poster_id for each topic_id?
by 
solistics
ANSWERS: 2
-
For example, I can do: SELECT MAX(post_time) FROM posts GROUP BY topic_id; to get the most recent post_time, but if I say: SELECT poster_id,MAX(post_time) FROM posts GROUP BY topic_id; it doesn't seem to give me the poster_id associated with the MAX(post_time).
-
I'm doing this off the top of my head without a database available to test on, but this is the gist of the approach. Assuming topic_id is a unique key, you use a subquery with the having clause: select topic_id, poster_id, post_id, post_time from table where topic_id = (select topic_id from table group by topic_id having max(post_time) ) ; Hollar back if that doesn't work and I'll get on a database and work it out.
RELATED QUESTIONS
Copyright 2023, Wired Ivy, LLC