ms sql select count, group by, top @var

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

Transact-SQL (2000)

ms sql select count, group by, top @var

Author

Topic

prusu83
Starting Member

1 Post

Posted - 2006-05-07 : 06:00:49

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.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-07 : 06:28:52

Is this what you want ? If not, post the table structure, some sample data and the expected result


select p.PostID, p.Title, p.Body, count(*) as noReplies
from   Table_Posts p inner join Table_PostsReplies r
on     p.PostID = r.PostID
where  p.PostID >= @startIndex
and    p.PostID <= @stopIndex
group by p.PostID, p.Title, p.Body

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources