I'm doing paging in sql query (refer to below sql)
I have controls on the page that let you set the number of records per page and the sort order
Let's say there are 5 pages returned
Each page itsself is sorted without regard for the overall picture
for example if viewing page 1 and sorting by a specific column and I click to sort by same column descending
I get the same records that were already shown on the page - just sorted backwards.
Q: how do I alter the sql (can't figure it out) so that when clicking to sort descending
it takes all records into account and displays the first @pagereturn (based on sort order) from all records?
In this example it would be the records that would have been on page 5.
DENSE_RANK() OVER(ORDER BY m.acolumn) AS OB,
DENSE_RANK() OVER(ORDER BY m.acolumn desc) AS OB2
From sometable m WITH (NOLOCK)
inner join anothertable c WITH (NOLOCK) on c.CatID = m.CatID
Where c.CatID = @CatID
thecolumms,OB2 + OB - 1 as numret
FROM List WITH (NOLOCK)
WHERE OB BETWEEN @pageStart AND (@pageStart + @pageReturn - 1)
case when @Orderby = ......