SQL Server Row_number() OVER with GROUP BY

Question

I have "GetConversationPaged" sp that selects paged data from table called "user_messages_index" and includes "lastmesage" in conversation from table called "social_user_messages". I'm not sure if it can be fine tuned but it works with one thing missing. I want to sort "Row_number" by lastMessageId which i get with select MAX(messageId).

PS: I have message index and messages tables because i wanted people to send same message to multiple recipients. Instead of inserting same message over and over i use index table

So, how can I sort results by latest message id?

---

Here are table and sps i'm using

--index table--

CREATE TABLE [dbo].[social_user_messages_index](
[senderId] [int] NOT NULL,
[recipientId] [int] NOT NULL,
[messageId] [int] NOT NULL,
[isRead] [bit] NOT NULL
 ) ON [PRIMARY]

--messages table--

CREATE TABLE [dbo].[social_user_messages](
[id] [int] IDENTITY(1,1) NOT NULL,
[message] [nvarchar](1000) NOT NULL,
[sendDate] [datetime] NOT NULL,
) ON [PRIMARY]

--sp--

ALTER PROCEDURE [dbo].[GetConversationPaged]
@userId int,
@isRead bit,
@PageNumber int,
@PageSize int
AS
BEGIN

    DECLARE @FirstRow  INT, @LastRow   INT, @RowCount  INT, @PageCount INT 
    --- find recordcount and pages 
    SELECT @RowCount = COUNT(1) OVER(), 
           @PageCount = COUNT(*) OVER() / @PageSize 
            FROM   social_user_mesages_index s
            WHERE  s.recipientId = @userId and s.isRead=@isRead
            GROUP BY senderId 
    --- calculate pages
    IF @RowCount % @PageSize != 0 SET @PageCount = @PageCount + 1 
    IF @PageNumber < 1  SET @PageNumber = 1 
    IF @PageNumber > @PageCount  SET @PageNumber = @PageCount 
    --- select paging data
    SELECT currentpage = @PageNumber, totalpages = @PageCount, totalrows = @RowCount 
    SET @FirstRow = ( @PageNumber - 1 ) * @PageSize + 1;
    SET @LastRow = ( @PageNumber - 1 ) * @PageSize + @PageSize; 

    --- select records     
    WITH mytable 
         AS (

            SELECT  Row_number() OVER (ORDER BY (SELECT 1)) AS rownumber,
                    (Select name from domains_users d where d.id=s.senderId) as senderName,
                    (select MAX(messageId)) as lastMessageId,
                    (select m.[message] from social_user_messages m where m.id = (select MAX(messageId))) as [message]
                    --,(select m.sendDate from social_user_messages m where m.id = (select MAX(messageId))) as lastMessageDate
                    ,senderId   
            FROM   social_user_mesages_index s
            WHERE  s.recipientId = @userId and s.isRead=@isRead
            GROUP BY senderId 

         ) 

    SELECT * 
    FROM   mytable 
    WHERE  rownumber BETWEEN @FirstRow AND @LastRow 
    ORDER  BY rownumber ASC; 
END

Solution

In your CTE, you had social_user_messages_index misspelled. I also removed the Group BY SenderId you had a few places, and updated the CTE. If this doesn't do what you were hoping, let me know and I can tweak it:

ALTER PROCEDURE [dbo].[GetConversationPaged]
@userId int,
@isRead bit,
@PageNumber int,
@PageSize int
AS
BEGIN

   DECLARE @FirstRow  INT, @LastRow   INT, @RowCount  INT, @PageCount INT 
   --- find recordcount and pages 
   SELECT @RowCount = COUNT(1) OVER()
      , @PageCount = COUNT(*) OVER() / @PageSize 
   FROM   dbo.social_user_messages_index AS s
   WHERE  s.recipientId = @userId and s.isRead=@isRead

   --- calculate pages
   IF @RowCount % @PageSize != 0 SET @PageCount = @PageCount + 1 
   IF @PageNumber < 1 SET @PageNumber = 1 
   IF @PageNumber > @PageCount SET @PageNumber = @PageCount 

   --- select paging data
   SELECT currentpage = @PageNumber
      , totalpages = @PageCount
      , totalrows = @RowCount;
   SET @FirstRow = ( @PageNumber - 1 ) * @PageSize + 1;
   SET @LastRow = ( @PageNumber - 1 ) * @PageSize + @PageSize; 

   --- select records     
   WITH mytable AS
   (
      SELECT Row_number() OVER (ORDER BY MessageId DESC) AS rownumber
         , du.name as senderName
         , m.Message
         , senderId
      FROM social_user_messages_index AS s
      INNER JOIN dbo.domains_users AS du ON s.SenderId = du.id
      INNER JOIN dbo.social_user_messages AS m ON s.messageId = m.id
      WHERE s.recipientId = @userId
         AND s.isRead=@isRead
   )
   SELECT * 
   FROM   mytable 
   WHERE  rownumber BETWEEN @FirstRow AND @LastRow 
   ORDER  BY rownumber ASC;
END

Comments

[Adam Wenger]

I also assumed dbo.domains_users is just id INT and name NVARCHAR since you did not supply more information about it.