SQL Group Clause - SQL Server Forums

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)

SQL Group Clause

Author

Topic

nanko
Starting Member

7 Posts

Posted - 2009-02-04 : 23:17:06

Hi, I am working on this Forums project where by I display the list of topics from main table and then topic link displaying page of replies. So there are 2 tables. The main topic list and the replies list.

My intent is to display Last posting username and datetime in the last column of main forum section. Just like you see on this forum. In MS Access I know there is a way where I can specify First or Last clause to display the record of a group. I dont know how I can do that in sql.

For example if topic 1 has 5 replies, then the last (latest) reply username and posting date should appear on the topic list.

Heres my code that I tried.

SELECT TopicsList.TopicID, TopicsList.Title, COUNT(TopicReplies.ReplyID) AS Replies, TopicsList.ViewCount, TopicsList.PostedBy, TopicsList.PostingDateTime,TopicsList.Active
FROM TopicsList INNER JOIN
TopicReplies ON TopicsList.TopicID = TopicReplies.TopicID
GROUP BY TopicsList.TopicID, TopicsList.Title, TopicsList.PostedBy, TopicsList.PostingDateTime, TopicsList.ViewCount, TopicsList.Active
HAVING (TopicsList.Active = 1)
ORDER BY TopicsList.PostingDateTime DESC

AvanthaSiriwardana
Yak Posting Veteran

78 Posts

Posted - 2009-02-04 : 23:32:14

SELECT TopicsList.TopicID, TopicsList.Title, COUNT(TopicReplies.ReplyID) AS Replies, TopicsList.ViewCount, TopicsList.PostedBy, TopicsList.PostingDateTime,TopicsList.Active
FROM TopicsList INNER JOIN
TopicReplies ON TopicsList.TopicID = TopicReplies.TopicID
WHERE(TopicsList.Active = 1)
GROUP BY TopicsList.TopicID, TopicsList.Title, TopicsList.PostedBy, TopicsList.PostingDateTime, TopicsList.ViewCount, TopicsList.Active
ORDER BY TopicsList.PostingDateTime DESC

Avantha Siriwardana
Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources