SQL Server Performance Forum – Threads Archive

Select help

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?

---

Select * from Forum_child T
where Datesubmitted=(Select max(Datesubmitted) from Forum_child where id=T.id) Madhivanan Failing to plan is Planning to fail

---

What you are doing works fine for the date submitted, but I also need the Submitter and there is no way to use max to get that value. Here is what I came up with throught trial and error and looking at some other posts. It seems to work: SELECT F.ID
, F.Title
, [Description] = F.[Description]
, Replies = ISNULL((SELECT COUNT(*) FROM Forum_Child WHERE PID = F.ID), 0)
, Views = ISNULL(Views, 0)
, Submitter = CASE WHEN ChildSub.Submitter IS NOT NULL THEN ChildSub.Submitter ELSE P.FirstName + ‘ ‘ + P.LastName END
, LastUpdated = CASE WHEN FFC.DateSubmitted IS NOT NULL THEN FFC.DateSubmitted ELSE F.DateSubmitted END
FROM Forum F
INNER JOIN Player P ON F.Submitter = P.ID
LEFT OUTER JOIN
(
SELECT PID, MAX(DateSubmitted) AS DateSubmitted, MAX(ID) AS ID
FROM Forum_Child
GROUP BY PID
) AS FFC ON F.ID = FFC.PID
LEFT OUTER JOIN
(
SELECT MAX(FC.ID) AS ID, Submitter = P.FirstName + ‘ ‘ + P.LastName
FROM Forum_Child FC
INNER JOIN Player P ON P.ID = FC.Submitter
Group by P.FirstName, P.LastName
) AS ChildSub ON FFC.ID = ChildSub.ID
ORDER BY LastUpdated DESC
I tried using a single join with a top 1 select query, but that doesn’t work because the query is being used as a join condition, and I end up matching that top 1 record for all records in the Forum Table. I think this works correctly.

---

]]>