April 4, 2006 at 2:46 pm
Hi, I've been racking my brain over this. Can't seem to find a solution. I want to pull the latest topics in which this user has posted.
tbl_Topics
topic_id
topic_title
tbl_Posts
post_id
post_time
topic_id
poster_id
Here is the query:
SELECT DISTINCT TOP 5 tbl_topics.topic_id, tbl_topics.topic_title, tbl_posts.post_time
FROM tbl_topics, tbl_posts
WHERE tbl_topics.topic_id = tbl_posts.topic_id
AND tbl_posts.poster_id = 9999
ORDER BY tbl_posts.post_time Desc
Here are the results I am getting:
TOPIC_ID TOPIC_TITLE POST_TIME
1 Topic 1 2pm
1 Topic 1 1pm
1 Topic 1 12pm
2 Topic 2 11am
3 Topic 3 10am
Here are the results I would like:
TOPIC_ID TOPIC_TITLE POST_TIME
1 Topic 1 2pm
2 Topic 2 11am
3 Topic 3 10am
4 Topic 4 9am
5 Topic 5 8am
Because a topic can have multiple posts, my query is pulling the same topic title for each post. I only want the topic to appear once even if there are multiple posts.
Many thanks to anyoine who can point me in the right direction.
April 4, 2006 at 3:02 pm
You can do a GROUP BY on TOPIC_ID and TOPIC_TITLE and select the MAX(POST_TIME) to get a single record per TOPIC_ID and TOPIC_TITLE and then chose the TOP 5 out of those based on whatever order you want.
April 4, 2006 at 3:07 pm
I don't have any data to try this with, but you may also try this approach:
SELECT topics.topic_id, topics.topic_title, posts.post_time
FROM tbl_topics topics
INNER JOIN( SELECT DISTINCT TOP 5 tbl_posts.topic_id, tbl_posts.post_time
FROM tbl_posts
ORDER BY tbl_posts.post_time DESC) posts ON( topics.topic_id = posts.topic_id)
WHERE posts.poster_id = 9999
(I am guessing rsharma is correct, though and you will want to use MAX( tbl_posts.post_time))
I wasn't born stupid - I had to study.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply