sql paging

web app:
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.


WITH List
AS
(
      Select
      some columns,
      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
)

      Select
      thecolumms,OB2 + OB - 1 as numret
      FROM List WITH (NOLOCK)
      WHERE OB BETWEEN @pageStart AND (@pageStart + @pageReturn - 1)
            
      ORDER BY
      case when @Orderby = ......
LVL 25
dgrafxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SharathData EngineerCommented:
Can you better explain with some sample set?
0
dgrafxAuthor Commented:
for ex
lets say returning 2 records per page
so heres page 1
1 one
2 two

page 2
3 three
4 four

page 3
5 five
6 six

ok - so then if you are on page one and sort descending you get
2 two
1 one

page two descending
4 four
3 three

do you see?

what I want to achieve is if sorting descending then page 1 would be
6 six
5 five

page two would be
4 four
3 three

or is there something different you are wanting to know?
0
SharathData EngineerCommented:
can you tell me what is @catid variable in your query? What are you passing in to that variable?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

dgrafxAuthor Commented:
it is an integer
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will need to order by in the inner query
WITH List
AS
(
      Select
      some columns,
      DENSE_RANK() OVER(ORDER BY case when @Orderby = ...... ) AS OB,
    DENSE_RANK() OVER(ORDER BY case when @Orderby = ...... ) AS OB2
      From sometable m WITH (NOLOCK)
      inner join anothertable c WITH (NOLOCK) on c.CatID = m.CatID
      Where c.CatID = @CatID
)
 
      Select
      thecolumms,OB2 + OB - 1 as numret
      FROM List WITH (NOLOCK)
      WHERE OB BETWEEN @pageStart AND (@pageStart + @pageReturn - 1)
            
      ORDER BY
      case when @Orderby = ......

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dgrafxAuthor Commented:
ok
I assumed that was what i needed to do but wondered if there was a "better" way.

Thanks much
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.