Get all topics, ordered by their last post date in a forum

I'm writing a forum. I've a TOPICS table, and a POSTS table. In the POSTS table, I have a TopicId field, and a date field.

I want to get all the topics, ordered by their last post date, in a single SQL query. How can I do this?

EDIT: The post's date is in the POST table. I want to check what is the last post of every post, then checking it's date, and then ordering the threads by this dat开发者_如何学Ce.

Here's a quick and dirty example:

CREATE TABLE dbo.Posts
(
    PostID INT,
    TopicID INT,
    AuthorID INT,
    PostDate SMALLDATETIME
);
GO
INSERT dbo.Posts SELECT 1,1,1,CURRENT_TIMESTAMP-1;
INSERT dbo.Posts SELECT 2,1,1,CURRENT_TIMESTAMP-0.5;
INSERT dbo.Posts SELECT 3,1,1,CURRENT_TIMESTAMP;
INSERT dbo.Posts SELECT 4,1,1,CURRENT_TIMESTAMP-2;
INSERT dbo.Posts SELECT 5,2,1,CURRENT_TIMESTAMP-0.75;
GO
;WITH x AS 
(
    SELECT
      TopicID,
      PostID,
      PostDate, 
      rn = ROW_NUMBER() OVER
      (
        PARTITION BY TopicID
        ORDER BY PostDate DESC
      )
    FROM dbo.Posts
)
SELECT TopicID, PostID, PostDate
    FROM x
    WHERE rn = 1;
GO

As for the indexed view suggested by hainstech, I imagine he meant something like this:

CREATE VIEW dbo.MaxPostDate
WITH SCHEMABINDING
AS
    SELECT 
        TopicID,
        MaxPostDate = MAX(PostDate)
    FROM
        dbo.Posts
    GROUP BY
        TopicID;
GO
CREATE UNIQUE CLUSTERED INDEX foo ON dbo.MaxDate(TopicID);
GO

Of course, this is not legal:

.Net SqlClient Data Provider: Msg 10125, Level 16, State 1, Line 1
Cannot create index on view "foo.dbo.MaxDate" because it uses aggregate "MAX".
Consider eliminating the aggregate, not indexing the view, or using alternate
aggregates. For example, for AVG substitute SUM and COUNT_BIG, or for COUNT,
substitute COUNT_BIG.

SELECT t.Name, MAX(p.Date)
FROM Topics t
JOIN Posts p ON p.TopicID = t.ID
GROUP BY t.Name
ORDER BY MAX(p.Date)

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

Hope this will help you

select ...,MAX(p.Date)
  from topics T 
  inner join posts P
  on T.TopicId = P.TopicId 
  Group by T.topicName
  order by Max(P.date)  

For most web apps similar to a forum you will want to employ something like an indexed view to maintain the latest post/thread per forum etc. Indexed views are nice for this because they are extremely quick to develop. Otherwise you can roll your own solution that maintains a similar denormalized view/table based on triggers (or sprocs/etc). Most forums I have seen have a 10:1 or 100:1 ratio of reads to writes, so this denormalized view/table is typically very worthwhile from a performance perspective.