SQL HELP getting duplicates when trying to get max(Date) for record

Question

Need some help, here is my SQL below: I am getting duplicates back and it will not return my LastRespondedDate Field. Any help would be greatly appreciated.

SELECT t.[column1],
       pr.[column1],
       pr.[RespondedDttm],
       t.[column2],
       t.[column3],
       t.[column4]
FROM Table1 t LEFT JOIN
(
    SELECT [t.column1], [pr.column2], [RespondedDttm], MAX([RespondedDttm]) AS LastRespondedDate
    FROM Table2 pr
    GROUP BY [column1], RespondedDttm, [pr.column3]) pr
    ON (t.[column1] = pr.[column1])
WHERE t.[column8] IN (value) AND
      (pr.[RespondedDttm] >= '2015-09-01') AND
      (pr.[Response] IS NOT Null)

Answer 1

SELECT
    t.[column1],
    pr.[RespondedDttm] as LastRespondedDate,
    t.[column2],
    t.[column3],
    t.[column4]
FROM
    Table1 t
    LEFT JOIN
    (
       SELECT
          t2.[Column1]
          ,t2.[column2]
          ,[RespondedDttm]
          ,RowNum = ROW_NUMBER() OVER (PARTITION BY ColWithDups ORDER BY [RespondedDttm])
          ,WithTiesRowNum = RANK() OVER (PARTITION BY ORDER BY [RespondedDttm] DESC) 
       FROM
          Table2 t2
       WHERE
          t2.[RespondedDttm] >= '2015-09-01'
          AND t2.[Response] IS NOT Null
    ) pr
    ON (t.[column1] = pr.[column1])
    AND pr.RowNum = 1
    --AND pr.WithTiesRowNum = 1 --use this line if you want ties
WHERE
    t.[column8] IN (value)

You can use window functions and ROW_NUMBER if you want only 1 row or RANK() if you want all ties.

In case you wanted the other direction of the nuance which I think you comment suggests:

SELECT
    t.[column1],
    pr.[RespondedDttm] as LastRespondedDate,
    t.[column2],
    t.[column3],
    t.[column4]
FROM
    Table1 t
    LEFT JOIN
    (
       SELECT
          t2.[Column1]
          ,t2.[column2]
          ,[RespondedDttm]
          ,RowNum = ROW_NUMBER() OVER (PARTITION BY ColWithDups ORDER BY [RespondedDttm])
          ,WithTiesRowNum = RANK() OVER (PARTITION BY ORDER BY [RespondedDttm] DESC) 
       FROM
          Table2 t2
    ) pr
    ON (t.[column1] = pr.[column1])
    AND pr.[RespondedDttm] >= '2015-09-01'
    AND pr.[Response] IS NOT Null
    AND pr.RowNum = 1
    --AND pr.WithTiesRowNum = 1 --use this line if you want ties
WHERE
    t.[column8] IN (value)