small but dificult ms sql select count, group by, top @var
-
i have Table_Posts(PostID, Title, Body) and Table_PostsReplies(PostID, ReplyPostID). i want to select the posts (postID, Title, Body, noReplies) starting with post @startIndex and finishing with @stopIndex.in ms sql i cannot use select count .. group by in a subselect.. also i cannot use top @variable only select top n.. please help how to select noPosts replies for each post and how to select posts from a range of indexes.
i need help writing this complex query.
-
SELECT p.PostID, p.Title, p.Body, COUNT(r.ReplyPostID)
FROM Table_Posts p
LEFT OUTER JOIN Table_PostsReplies r
ON r.PostID = p.PostID
WHERE p.PostID BETWEEN @startIndex AND @stopIndex
GROUP BY p.PostID, p.Title, p.Body
Far away is close at hand in the images of elsewhere.
Anon. -
Too bad... I think this was homework and didn't see where Paul tried...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Yeah possibly
Must admit I do not look to see if it's any form of homework/coursework
Unless you have done homework like this then it is difficult to tell
Besides if people do this to solve their homework then they are only cheating themselves and will learn nothing
Far away is close at hand in the images of elsewhere.
Anon. -
It's no homework.
I did try something like this, well with normal join sintax.
One of the problems is p.PostID is not equal to row index. I want to select posts from @startIndex'th post to @stopIndex'th post.
-
SELECT p.PostID, p.Title, p.Body, COUNT(r.ReplyPostID) AS [noReplies]
FROM Table_Posts p
LEFT OUTER JOIN Table_PostsReplies r
ON r.PostID = p.PostID
GROUP BY p.PostID, p.Title, p.Body
HAVING (SELECT COUNT(*) FROM Table_Posts p2 WHERE p2.PostID <= p.PostID) BETWEEN @startIndex AND @stopIndex
Far away is close at hand in the images of elsewhere.
Anon. -
That's elegant. Thanks a lot man.
-
Actually there's one more problem.
If table Table_PostsReplies is empty it won't select any posts.
I want to be able to select posts even though they have no reply in the replies table. How to do this without too many complications - cursors.. and stuff.
-
Actually David's solution suppose to do exactly what you are asking for.
You must have modified it a little, so it now requires replies.
Return to the original query logic.
_____________
Code for TallyGenerator
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply