GROUP BY in SQL Server in complex query

Question

I need to group this by T.TopicID to only receive the last result.

Whatever I try I get errors like the other T. items rant included in group by or aggregate etc

ALTER PROCEDURE [dbo].[SPGetFollowingTopics]
    @id int = null
    ,@UserGroupId int = null
    ,@lastvisit DateTime = null
AS
    SELECT *
    FROM
       (SELECT
           ROW_NUMBER() OVER (ORDER BY TopicOrder DESC,
                                    (CASE 
                                        WHEN M.MessageCreationDate > T.TopicCreationDate 
                                          THEN M.MessageCreationDate
                                          ELSE T.TopicCreationDate
                                      END) DESC) AS RowNumber, 
           T.TopicId, T.TopicTitle, T.TopicShortName,  
           T.TopicDescription, T.TopicCreationDate, T.TopicViews, 
           T.TopicReplies, T.UserId, T.TopicTags, T.TopicIsClose, 
           T.TopicOrder, T.LastMessageId, U.UserName, 
           M.MessageCreationDate, T.ReadAccessGroupId, 
           T.PostAccessGroupId, TF.userid AS Expr1, U.UserGroupId, 
           U.UserPhoto, U.UserFullName, M.UserId AS MessageUserId,
           MU.UserName AS MessageUserName
        FROM            
           Topics AS T 
        LEFT OUTER JOIN
           Messages AS M ON M.TopicId = T.TopicId AND M.Active = 1 AND M.MessageCreationDate < @lastvisit
        INNER JOIN 
           topicfollows AS TF ON T.TopicId = TF.topicid    
        INNER JOIN
           Users AS U ON U.UserId = T.UserId
        LEFT JOIN 
           Users MU ON MU.UserId = M.UserId
        WHERE
           (TF.userid = @id) 
       ) T

Answer 1

It isn't clear what the requirement is (in my view) but I think you are seeking:

1. "the latest message"
2. PER TOPIC
3. for a given user

In this situation ROW_NUMBER() is a good option but I believe you need to PARTITION the ROW_NUMBER as well as ordering it.

SELECT
      *
FROM (
      SELECT
            ROW_NUMBER() OVER (PARTITION BY TF.userid, T.TopicId 
                               ORDER BY
                                    (CASE
                                          WHEN M.MessageCreationDate > T.TopicCreationDate THEN M.MessageCreationDate
                                          ELSE T.TopicCreationDate
                                    END) DESC) AS ROWNUMBER
          , T.TopicId, T.TopicTitle, T.TopicShortName, T.TopicDescription
          , T.TopicCreationDate, T.TopicViews, T.TopicReplies, T.UserId
          , T.TopicTags, T.TopicIsClose, T.TopicOrder, T.LastMessageId
          , U.UserName, M.MessageCreationDate, T.ReadAccessGroupId
          , T.PostAccessGroupId, TF.userid AS EXPR1
          , U.UserGroupId, U.UserPhoto, U.UserFullName
          , M.UserId AS MESSAGEUSERID, MU.UserName AS MESSAGEUSERNAME
      FROM Topics AS T
      LEFT OUTER JOIN Messages AS M ON M.TopicId = T.TopicId
                  AND M.Active = 1
                  AND M.MessageCreationDate < @lastvisit
      INNER JOIN topicfollows AS TF ON T.TopicId = TF.topicid
      INNER JOIN Users AS U ON U.UserId = T.UserId
      LEFT JOIN Users MU ON MU.UserId = M.UserId
      WHERE (TF.userid = @id)
) T
WHERE ROWNUMBER = 1

Answer 2

You could change your left join to any outer apply, and add TOP 1:

SELECT ...
FROM            
    Topics AS T 
OUTER APPLY
(   SELECT  TOP 1 M.MessageCreationDate, M.UserId
    FROM    Messages AS M 
    WHERE   M.TopicId = T.TopicId 
    AND     M.Active = 1 
    AND     M.MessageCreationDate < @lastvisit
    ORDER BY M.MessageCreationDate DESC
) AS m

This allows you to use TOP 1 and still get one row per topicID

Alternatively you can use ROW_NUMBER() OVER(PARTITION BY m.TopicID ORDER BY M.MessageCreationDate DESC)

SELECT ...
FROM            
    Topics AS T 
LEFT OUTER JOIN
(   SELECT  M.TopicId,
            M.MessageCreationDate, 
            M.UserId, 
            RowNum = ROW_NUMBER() OVER(PARTITION BY m.TopicID ORDER BY M.MessageCreationDate DESC)
    FROM    Messages AS M 
    WHERE   M.Active = 1
    AND     M.MessageCreationDate < @lastvisit
) AS m
    ON M.TopicId = T.TopicId 
    AND m.RowNum = 1

I would test both methods and see which one works best for you.