• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

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 = ......
0
dgrafx
Asked:
dgrafx
  • 3
  • 2
1 Solution
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
dgrafxAuthor Commented:
ok
I assumed that was what i needed to do but wondered if there was a "better" way.

Thanks much
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now