StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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. </p> <p>Essentially you're creating a table with a topic ID, and the MAX(date), and joining against that.</p> <p>Example script:</p> <pre><code>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 </code></pre> <p>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.</p> <pre><code>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 </code></pre> <p>Kris</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload