Select Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Select Query

Author

Topic

jimsurf
Starting Member

18 Posts

Posted - 2006-09-18 : 18:48:09

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?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-18 : 19:10:54

SELECT PID, MAX(DateSubmitted)
From Forum_Child
GROUP BY PID

Tara Kizer

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-18 : 19:22:39

[code]
select f.*
from Forum_Child f
inner join
(
select PID, max(DateSubmitted) as max_datesubmitted
from Forum_Child
group by PID
) m
on f.PID = m.PID
and f.DateSubmitted = m.max_datesubmitted
[/code]

jimsurf
Starting Member

18 Posts

Posted - 2006-09-18 : 21:40:59

Thanks for the input, the problem was that I also needed the Submitter from the child table and there was know way to use max to get it. 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.

Is it optimized?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-18 : 23:09:43

try this


SELECT	F.ID, 
	F.Title, 
	[Description] 	= F.[Description], 
	FFC.Replies, 
	[Views] 	= ISNULL([Views], 0), 
	Submitter 	= coalesce(ChildSub.Submitter, P.FirstName + ' ' + P.LastName),
	LastUpdated 	= coalesce(FFC.DateSubmitted, F.DateSubmitted)
FROM	Forum as F
	INNER JOIN Player as P	ON F.Submitter	= P.ID
	LEFT OUTER JOIN
	(
		SELECT 	PID, MAX(DateSubmitted) AS DateSubmitted, count(*) as Replies
		FROM 	Forum_Child
		GROUP BY PID
	) as FFC			ON F.ID	= FFC.PID
	LEFT OUTER JOIN
	(
		select	ID, Submitter = P.FirstName + ' ' + P.LastName
		from	Forum_Child FC
			INNER JOIN Player P	ON FC.Submitter = P.ID
	) as ChildSub		ON FFC.ID	= ChildSub.ID
ORDER BY LastUpdated DESC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-18 : 23:10:44

If it is not what you are after, try posting the table DDL, some sample data and the result that you want

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources