Help with getting top 5 forum posts

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 2008 Forums

Transact-SQL (2008)

Help with getting top 5 forum posts

Author

Topic

meckeard
Starting Member

4 Posts

Posted - 2012-06-12 : 09:17:04

Hi all,

I have the following table called Forum_Posts:

PostID (pk)
Subject
Body
CreatedDate
ThreadID (fk)

I'm trying to get a list of top 5 Threads with the number of replies it has along with the subject, body and CreatedDate.

Getting a distinct list of the 5 ThreadID's is OK until I introduce any other field other than the number of replies. At that point I get duplicates of ThreadID's.

I've tried a few different solutions but with no luck. My preference would be to do this without numerous subqueries since it seems like it can be done without them.

Here's my base query using a CTE that returns the ID's for my only 3 threads and the number of replies for each one:

with ThreadTable(ThreadID, ThreadCount)
as
(
select distinct FP.ThreadID, count(*)-1 as ReplyCount from dbo.Forum_Posts FP group by FP.ThreadID
)
select *
from ThreadTable TT
order by TT.ThreadID desc

What do I need to add to get the remaining fields from Forum_Posts?

Thanks,
Mark

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-12 : 12:02:50

select top 5 FP.ThreadID, count(*)-1 as ReplyCount, ....
from dbo.Forum_Posts FP
group by FP.ThreadID
order by ReplyCount desc

You will need to use an aggregate (probably max or min) to ge the single row value from the group of thread replies for the thread id.

could also
with cte as
(
select top 5 FP.ThreadID, count(*)-1 as ReplyCount, ....
from dbo.Forum_Posts FP
group by FP.ThreadID
order by ReplyCount desc
)
select *, (select min(replyid) from Forum_Posts FP where FP.ThreadID = cte.ThreadID)
from cte

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

meckeard
Starting Member

4 Posts

Posted - 2012-06-12 : 14:05:48

Your reply got me wondering about the role of the PostID and I decided to add it to CTE and this worked:

with ThreadTable(ThreadID, MaxPostID, ThreadCount)
as
(
select top 5 FP.ThreadID, max(PostID) as MaxPostID, count(*)-1 as ReplyCount
from dbo.Forum_Posts FP
group by FP.ThreadID
order by ReplyCount desc
)
select TT.ThreadID, TT.MaxPostID, TT.ThreadCount, FP.Subject, FP.CreatedDate
from ThreadTable TT
JOIN Forum_Posts FP on TT.ThreadID = FP.ThreadID
where TT.MaxPostID = FP.PostID

Thanks for the help!

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources