SQL Server Performance

Select help

Discussion in 'T-SQL Performance Tuning for Developers' started by jimsurf, Sep 18, 2006.

  1. jimsurf New Member

    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?

  2. Madhivanan Moderator

    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
  3. jimsurf New Member

    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.

Share This Page