Best way to get last post in topic

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Best way to get last post in topic

Author

Topic

dbwilson4
Yak Posting Veteran

50 Posts

Posted - 2007-07-20 : 13:38:31

I have a forum posts table:
[posts]
id
topicid
subject
message
created_date

The "topicid" is actually a reference to a post in the posts table (since a topic is also considered a post itself).

I want to get the last POST when given a topicid

SELECT TOP 1 * FROM posts WHERE topicid = 3 ORDER BY created_date DESC

would work BUT THE problem is how do I get the LAST post when given a BUNCH OF topic ids?? (THe above query only works when I have only 1 topic id)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-20 : 14:25:10

SELECT TopicID, MAX(Created_Date)
FROM Posts
WHERE TopicID IN (<List of topics here>)
GROUP BY TopicID

Peter Larsson
Helsingborg, Sweden

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-20 : 14:29:44

Something like this:


SELECT  P. * 
FROM    Posts P 
JOIN(SELECT PostId, TopicId, MAX(Created_Date) FROM posts GROUP BY PostId, TopicId) 
        P2 
ON      P.Postid       = P2.PostId 
    AND P.TopicId      = P2.TopicId 
    AND P.Created_Date = P2.Created_date

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources