How to get row number based on unique field and sorted by another field

Question

i want to get this kind of result using MS SQL

PAGE 1
ID     Research Title       Published Date    Author ID   Author Name
1      Research A           2013-5-10          D567           John
1      Research A           2013-5-10          D568           Stan
2      Research B           2013-5-9           D569           Steve
3      Research C           2013-5-8           D570           Michael

PAGE 2
ID     Research Title       Published Date    Author ID   Author Name
4      Research D           2013-5-8           D567           John
5      Research F           2013-5-7           D570           Michael
5      Research F           2013-5-7           D568           Stan    
5      Research F           2013-5-7           D569           Steve    
6      Research E           2013-5-6           D569           Steve

The result is a combination of 3 Table

Table "Research" that holds all research data

------ TABLE Research------
- ResearchID Uniqueidentifier
- ResearchTitle text
- PublishedDate

Table "Author" That holds all lecturer data

------ TABLE Author ------
- AuthorID varchar(20)
- AuthorName vachar(100)

And, Table "ResearchAuthorMapping" that create relation between "Research" And "Author"

------ TABLE ResearchAuthorMapping------
- ResearchID uniqueidentifier    
- AuthorID varchar(20)

Here is my query so far

DECLARE @offset int;
DECLARE @limit int = 3;
DECLARE @page int = 1 -- CHANGE BY REQUEST
SET @offset = ((@page - 1) * @limit) + 1;
SELECT * 
FROM
(
    SELECT
        DENSE_RANK() OVER(ORDER BY Research.ResearchTitle DESC) AS num,
        Research.*
    FROM
        Research
        LEFT JOIN
            ResearchAuthorMapping ON Research.ResearchID = ResearchAuthorMapping.ResearchID
        LEFT JOIN
            Author ON ResearchAuthorMapping.AuthorID = Author.AuthorID

)TempTable
WHERE
    TempTable.num >= @Offset AND TempTable.num < @offset + @limit

Until now, i'm only able to achieve this

PAGE 1
ID     Research Title       Published Date    Author ID   Author Name
1      Research F           2013-5-7           D570           Michael
1      Research F           2013-5-7           D568           Stan
1      Research F           2013-5-7           D569           Steve    
2      Research E           2013-5-6           D569           Steve    
3      Research D           2013-5-8           D567           John

PAGE 2
ID     Research Title       Published Date    Author ID   Author Name
4      Research C           2013-5-8           D570           Michael
5      Research B           2013-5-9           D569           Steve
6      Research A           2013-5-10          D567           John
6      Research A           2013-5-10          D568           Stan

A result set that's ordered by Research Title,

But, What i want is the data ordered by Published date, but getting the ID based on

ResearchTitle / ResearchID grouping (in my query i get the ID by using DENSE_RANK). i use the ID for limiting the result (pagination)

*Sorry for my bad english

Answer 1

I recon you could wrap your results in a row_number set that re-orders the initial results. Something along the lines of:

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY PublishedDate ) AS rownum
                   ,A.*
          FROM      ( SELECT    DENSE_RANK() OVER ( ORDER BY Research.ResearchTitle DESC ) AS num
                               ,ResearchID
                               ,PublishedDate
                      FROM      Research ) A ) B
LEFT JOIN ResearchAuthorMapping C
ON      B.ResearchID = C.ResearchID
LEFT JOIN Author D
ON      C.AuthorID = D.AuthorID
WHERE   B.rownum >= @Offset
        AND B.rownum < @offset + @limit

I moved the joins outside of the subquery to limit the lookups.