🚀 go-pugleaf

I was wondering if you could help me solve a SQL problem that I have been
unable find a solution for anywhere. I am designing a web based forum
system. The database for the forum will have two main tables:



tblTopic

ID         Int / PrimaryKey (Auto Increment)

Title      Text

PostDate   DateTime

...



tblReply

ID         Int / PrimaryKey (Auto Increment)

PostDate   DateTime

TopicID    Int

...



There will be a "one to many" relationship between tblTopic.ID and
tblReply.TopicID



Given table tblTopic with data:

ID       Title         PostDate

1        Topic 1       1/1/2002 1:00:00 AM

2        Topic 2       1/1/2002 1:01:00 AM

3        Topic 3       1/1/2002 1:06:00 AM



And table tblReply with data:

ID         PostDate                TopicID

1          1/1/2002 1:02:00 AM     1

2          1/1/2002 1:03:00 AM     1

3          1/1/2002 1:04:00 AM     2

4          1/1/2002 1:05:00 AM     1



I need to produce a SQL statement to return Topics ordered in descending
order by last topic or last reply date:

tblTopic.ID   tblTopic.Title   LastPostDate          NumberOfReplies

3             Topic 3          1/1/2002 1:06:00 AM   0

1             Topic 1          1/1/2002 1:05:00 AM   3

2             Topic 2          1/1/2002 1:04:00 AM   1





I have come up with an SQL statement to produce the above result but it
involves nested queries and is very CPU intensive. I am sure a more elegant
solution is possible, if you can help me solve this it would be greatly
appreciated.



Table schemas and sample data below:

====================================

CREATE TABLE tblTopic
(
ID int primary key NOT NULL IDENTITY (1, 1),
Title nvarchar(100) NOT NULL,
PostDate datetime NOT NULL
)
CREATE TABLE tblReply
(
ID int primary key NOT NULL IDENTITY (1, 1),
PostDate datetime NOT NULL,
TopicID int NOT NULL
)
ALTER TABLE tblReply ADD CONSTRAINT
FK_tblReply_tblTopic FOREIGN KEY
(
TopicID
) REFERENCES dbo.tblTopic
(
ID
)
GO

INSERT INTO tblTopic (Title, PostDate) VALUES ('Topic 1','1/1/2002 1:00:00
AM')
INSERT INTO tblTopic (Title, PostDate) VALUES ('Topic 2','1/1/2002 1:01:00
AM')
INSERT INTO tblTopic (Title, PostDate) VALUES ('Topic 3','1/1/2002 1:06:00
AM')
INSERT INTO tblReply (PostDate, TopicID) VALUES ('1/1/2002 1:02:00 AM', 1)
INSERT INTO tblReply (PostDate, TopicID) VALUES ('1/1/2002 1:03:00 AM', 1)
INSERT INTO tblReply (PostDate, TopicID) VALUES ('1/1/2002 1:04:00 AM', 2)
INSERT INTO tblReply (PostDate, TopicID) VALUES ('1/1/2002 1:05:00 AM', 1)
GO