There are a couple of ways to do what you're asking. I use a derived table to do things like this, although if you're using SQL Server 2005 you can do this with a Common Table Expression.
Essentially you're creating a table with a topic ID, and the MAX(date), and joining against that.
Example script:
If (object_ID('Posts') is not null) drop table Posts
If (object_ID('Topics') is not null) drop table Topics
GO
create table Topics (
TopicID int identity(1,1) primary key,
TopicName varchar(100))
go
Create Table Posts (
PostID int identity(1,1) primary key,
TopicID int references Topics,
Title varchar(100),
PostDate datetime)
GO
Insert into Topics(TopicName) Values ('Math')
Insert into Topics(TopicName) Values ('English')
Insert into Topics(TopicName) Values ('Other')
Insert into Posts(TopicID, Title, PostDate) values (1, 'On numbers 1', GETDATE()-7)
Insert into Posts(TopicID, Title, PostDate) values (1, 'On numbers 2', GETDATE()-2)
Insert into Posts(TopicID, Title, PostDate) values (2, 'On words 1', GETDATE()-4)
Insert into Posts(TopicID, Title, PostDate) values (2, 'On words 2', GETDATE())
Insert into Posts(TopicID, Title, PostDate) values (3, 'WTF? 1', GETDATE()-3)
Insert into Posts(TopicID, Title, PostDate) values (3, 'WTF? 2', GETDATE()-1)
GO
--Derived table
Select TopicName, LastPostDate from Topics T
Inner join (Select TopicID, MAX(PostDate) as LastPostDate
from Posts P
group by TopicID) as LastPostTable
on T.TopicID=LastPostTable.TopicID
order by LastPostDate desc;
--CTE (SQL Server 2005+)
With CTE_LastPostTable (TopicID, LastPostDate)
as
(Select TopicID, MAX(PostDate) as LastPostDate
from Posts P
group by TopicID)
Select TopicName, LastPostDate=coalesce(LastPostDate, '1899-01-01') from Topics T
Left outer join CTE_LastPostTable CTE
on T.TopicID=CTE.TopicID
order by LastPostDate desc
You can switch the inner join with a left outer join, and a coalesce around the date column to capture any topics that don't yet have posts.
With CTE_LastPostTable (TopicID, LastPostDate)
as
(Select TopicID, MAX(PostDate) as LastPostDate
from Posts P
group by TopicID)
Select TopicName, LastPostDate=coalesce(LastPostDate, '1899-01-01') from Topics T
Left outer join CTE_LastPostTable CTE
on T.TopicID=CTE.TopicID
order by LastPostDate desc
Kris