How to get line number based on a unique field and sort by another field

Question

I want to get this 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 tables

The "Research" table, which contains all the research data

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

Table "Author", which contains all the lecturer's data.

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

And, the "ResearchAuthorMapping" table, which create the link between "Research" and "Author"

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

Here is my request 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 

So far i can 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 set of results ordered by study title,

But what I want is data sorted by publication date, but getting an identifier based on

ResearchTitle / ResearchID grouping (in my request, I get the identifier using DENSE_RANK). I use an identifier to limit the result (pagination)

* Sorry for my bad english

Answer 1

I conclude that you can wrap your results in a row_number set that reorders the initial results. Sort 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 have moved the connections outside the subquery to limit the search.