September 18, 2006 at 4:59 pm
Forum Child:
CREATE TABLE [dbo].[Forum_Child](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PID] [int] NOT NULL,
[Post] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateSubmitted] [datetime] NOT NULL,
[Submitter] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
this is a child table to Forum with PID being the Foreign Key.
It is a one to many relationship. For each post in the Forum table,
I can have 0-many replies in this child table.
How do I write a query to select the most recent record for each unique PID.
I want a maximum of one record per PID and I want it to be the most recent.
For example:
Select TOP 1 *
From Forum_Child
where PID = 3
ORDER BY DateSubmitted DESC
except this only returns the most recent child record for Post 3. I want a query that will give me this result for each distinct PID in the table
Any Ideas?
September 18, 2006 at 7:44 pm
Select *
From Forum_Child
join (Select max(ID) as LastID, PID
From Forum_Child
Group by PID) T on LastID = ID
September 19, 2006 at 5:23 am
SELECT * FROM Formum_Child a INNER JOIN
(SELECT pid, MAX(DateSubmitted) DateSubmitted FROM Forum_Child GROUP BY pid) b
ON a.pid=b.pid AND a.DateSubmitted=b.DateSubmitted
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply