June 12, 2012 at 7:18 am
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
June 12, 2012 at 7:26 am
could you provide some sample data and the expected outcome based on that sample data (see the second link in my signature for help should you need it)
June 12, 2012 at 7:26 am
Hi,
Can you post a sample data table and the expected result?
June 12, 2012 at 7:32 am
I think you could use table that has ThreadID as PK to get top 5 thread and then retrive details from Forum_Posts table.
In your code example you can do something like this...
select FP.PostID,FP.Subject,FP.Body,FP.CreatedDate,FP.ThreadID
from ThreadTable TT
JOIN Forum_Posts FP on TT.ThreadID =FP.ThreadID
order by TT.ThreadID desc
June 12, 2012 at 7:45 am
this is my take on sample data and a solution
DECLARE @Forum_Posts TABLE (PostID INT IDENTITY, Subject NVARCHAR(100), Body NVARCHAR(100), CreatedDate DATETIME, ThreadID INT)
INSERT INTO @Forum_Posts VALUES ('Sub1','Bod1',GETDATE(),1)
INSERT INTO @Forum_Posts VALUES ('Sub2','Bod2',GETDATE(),1)
INSERT INTO @Forum_Posts VALUES ('Sub3','Bod3',GETDATE(),1)
INSERT INTO @Forum_Posts VALUES ('Sub4','Bod4',GETDATE(),1)
INSERT INTO @Forum_Posts VALUES ('Sub5','Bod5',GETDATE(),1)
INSERT INTO @Forum_Posts VALUES ('Sub6','Bod6',GETDATE(),1)
INSERT INTO @Forum_Posts VALUES ('Sub1','Bod1',GETDATE(),2)
INSERT INTO @Forum_Posts VALUES ('Sub2','Bod2',GETDATE(),2)
INSERT INTO @Forum_Posts VALUES ('Sub3','Bod3',GETDATE(),2)
INSERT INTO @Forum_Posts VALUES ('Sub4','Bod4',GETDATE(),2)
INSERT INTO @Forum_Posts VALUES ('Sub5','Bod5',GETDATE(),2)
INSERT INTO @Forum_Posts VALUES ('Sub6','Bod6',GETDATE(),2)
INSERT INTO @Forum_Posts VALUES ('Sub1','Bod1',GETDATE(),3)
INSERT INTO @Forum_Posts VALUES ('Sub2','Bod2',GETDATE(),3)
INSERT INTO @Forum_Posts VALUES ('Sub3','Bod3',GETDATE(),3)
INSERT INTO @Forum_Posts VALUES ('Sub4','Bod4',GETDATE(),3)
INSERT INTO @Forum_Posts VALUES ('Sub5','Bod5',GETDATE(),3)
INSERT INTO @Forum_Posts VALUES ('Sub6','Bod6',GETDATE(),3)
INSERT INTO @Forum_Posts VALUES ('Sub1','Bod1',GETDATE(),4)
INSERT INTO @Forum_Posts VALUES ('Sub2','Bod2',GETDATE(),4)
INSERT INTO @Forum_Posts VALUES ('Sub3','Bod3',GETDATE(),4)
INSERT INTO @Forum_Posts VALUES ('Sub4','Bod4',GETDATE(),4)
INSERT INTO @Forum_Posts VALUES ('Sub5','Bod5',GETDATE(),4)
INSERT INTO @Forum_Posts VALUES ('Sub6','Bod6',GETDATE(),4)
INSERT INTO @Forum_Posts VALUES ('Sub1','Bod1',GETDATE(),5)
INSERT INTO @Forum_Posts VALUES ('Sub2','Bod2',GETDATE(),5)
INSERT INTO @Forum_Posts VALUES ('Sub3','Bod3',GETDATE(),5)
INSERT INTO @Forum_Posts VALUES ('Sub4','Bod4',GETDATE(),5)
INSERT INTO @Forum_Posts VALUES ('Sub5','Bod5',GETDATE(),5)
INSERT INTO @Forum_Posts VALUES ('Sub6','Bod6',GETDATE(),5)
INSERT INTO @Forum_Posts VALUES ('Sub1','Bod1',GETDATE(),6)
INSERT INTO @Forum_Posts VALUES ('Sub2','Bod2',GETDATE(),6)
INSERT INTO @Forum_Posts VALUES ('Sub3','Bod3',GETDATE(),6)
INSERT INTO @Forum_Posts VALUES ('Sub4','Bod4',GETDATE(),6)
INSERT INTO @Forum_Posts VALUES ('Sub5','Bod5',GETDATE(),6)
/*Expected results, ThreadID's 1,2,3,4,5 along with the number of posts, post subjects, post bodies and post createddate*/
;WITH CTE1 AS
(
SELECT
COUNT(*) AS NumberOfPosts,
ThreadID
FROM
@Forum_Posts
GROUP BY
ThreadID
)
, CTE2 AS
(
SELECT
RANK() OVER(ORDER BY NumberOfPosts DESC) AS Ranking,
NumberOfPosts,
ThreadID
FROM
cte1
)
SELECT
cte.NumberOfPosts,
cte.ThreadID,
fp.Subject,
fp.Body,
fp.CreatedDate
FROM
cte2 cte
INNER JOIN
@Forum_Posts fp
ON
CTE.ThreadID = fp.ThreadID
WHERE
cte.Ranking <= 5
ORDER BY
1 desc,2
June 12, 2012 at 9:45 am
Hey guys,
Here's a sample of my data:
PostID | ParentPostID | Subject | Body | CreatedDate | ThreadID
========================================================================
1 | 0 | Post 1 | This is the body | 6/12/2012 | 1
2 | 0 | Post 2 | This is the body | 6/12/2012 | 2
3 | 0 | Post 3 | This is the body | 6/12/2012 | 3
4 | 3 | Post 3, replay 1 | This is the body | 6/12/2012 | 3
Note that there is a post for ThreadID 3 and 1 reply to it. These are the last 2 records above.
Here's what I'd like to get back from my query:
PostID | Subject | Body | CreatedDate | ThreadID | ReplyCount
========================================================================
1 | Post 1 | This is the body | 6/12/2012 | 1 | 0
2 | Post 2 | This is the body | 6/12/2012 | 2 | 0
3 | Post 3 | This is the body | 6/12/2012 | 3 | 1
Basically 1 record for each ThreadID with some of the basic data along with the number of replies for that thread (ReplyCount).
Thanks,
Mark
June 12, 2012 at 10:48 am
Notice how Anthony posted sample data? It is readily consumable so nobody has to generate based off what you typed. From your description it seems like you could use something like this to get what you are looking for.
select Subject, ThreadID, COUNT(*) - 1 as ReplyCount
from @Forum_Posts
group by Subject, ThreadID
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 12, 2012 at 11:51 am
All,
I'm still getting numerous records per ThreadID when I'm only interested in 1.
I'll keep at it and appreciate all the suggestions.
Mark
June 12, 2012 at 12:00 pm
Given this table structure:
PostID | ParentPostID | Subject | Body | CreatedDate | ThreadID
Do you have any data where ParentPostID goes up to a row that also has a parent? Like a reply to a reply? If so, do you want the top level post, or the intermediate post?
Example:
Post 1
Post 2, Parent = Post 1
Post 3, Parent = Post 2
In that scenario, would you count Post 1 as having 2 replies (posts 2 & 3), or would you count Post 1 and Post 2 as having 1 reply each?
That will determine how to query the whole thing.
EDIT: Or would you count Post 1 as having 2 replies (2 & 3), and Post 2 as having 1 (Post 3)?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 12, 2012 at 12:05 pm
All these replies 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 everyone for the help!
Mark
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply