views:

116

answers:

5

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 date.

A: 
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)
anishmarokey
x = the thread's post count. It will return every thread x times.
TTT
Just now only i saw your edit ..
anishmarokey
A: 
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)
Michael Petrotta
A: 

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

KSimons
A: 

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.

Indexed views can maintain aggregates like COUNT_BIG(), but how do you propose to use an indexed view to keep track of the MAX(PostDate)?CREATE TABLE dbo.x( a INT, b SMALLDATETIME);GOCREATE VIEW dbo.yWITH SCHEMABINDINGAS SELECT a, b = MAX(b) FROM dbo.x GROUP BY a;GOCREATE UNIQUE CLUSTERED INDEX foo ON dbo.x(a);GOCannot 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.
Aaron Bertrand
Sorry, formatting is horrible. I'll show my repro in an answer.
Aaron Bertrand
@Aaron, of course you are 100% correct. My most recent usage of an indexed view was over a year ago and I seem to have forgotten these basic limitations. Thanks for your excellent disproof :)I still recommend the denormalized most-recent-post be maintained by whatever means (trigger / sproc / nanny process). Not that this proves it a best practice, but I recall seeing this same approach in the vbulletin schema.
+1  A: 

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.
Aaron Bertrand