title: SQL Server Row_number() OVER with GROUP BY
body: <p>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).</p>
  
  <p>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</p>
  
  <p><strong>So, how can I sort results by latest message id?</strong></p>
  
  <hr>
  
  <p>Here are table and sps i'm using</p>
  
  <p>--index table--   </p>
  
  <pre><code>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]
  </code></pre>
  
  <p>--messages table--</p>
  
  <pre><code>CREATE TABLE [dbo].[social_user_messages](
  [id] [int] IDENTITY(1,1) NOT NULL,
  [message] [nvarchar](1000) NOT NULL,
  [sendDate] [datetime] NOT NULL,
  ) ON [PRIMARY]
  </code></pre>
  
  <p>--sp--</p>
  
  <pre><code>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 &lt; 1  SET @PageNumber = 1 
      IF @PageNumber &gt; @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
  </code></pre>