Results 1 to 11 of 11
  1. #1
    Join Date
    May 2006
    Posts
    5

    Unanswered: Paging large Results in SQL 2005

    lets say we have more than 100 000 rows in Table1, and we want to view each 10 rows alone.... and by pressing on a NEXT button we will see the other 10 pages....

    there is 2 buttons : NEXT and PREVIOUS

    so can anyone tell me how to do that in SQL 2005, and what is correctly called.

    I have found a code that does use ROW_NUMBER in order to view results between 2 numbers,

    example: rows between 10 and 50....
    but It is not what I want, so please I need some help, thank you

    By Uncle Sam

  2. #2
    Join Date
    May 2006
    Posts
    16
    If you code this by program, you can use ADO object that contains "PageSize" property to set how many records could be shown in a page.

  3. #3
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    you can do some trick with the NTILE function to get the result. though it wont perform good. also with NTILE function you need back calculate the number of pages if 10 rows per page is to be displayed.....

    select * from (select *,ntile(2) over(order by COL1) as PgNo from test) as TempTbl where TempTbl.PgNo = 2

  4. #4
    Join Date
    May 2006
    Posts
    5
    Ok can you tell me how to do that, because I am beginner

  5. #5
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    +1 : bad idea to solve this issue using cursor or any other server-side trick. You must manage that in your client-side...
    F. Celaia
    DBA Sybase/DB2/Oracle/MS-SQL

  6. #6
    Join Date
    May 2006
    Posts
    5
    ok but is there any Stored procedure that does it

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Order your result set by a primary (natural) key. Write your NEXT procedure to take a starting key and a requested record count and return that number of records starting at that point in the result set. Your application just needs to know that last pkey it received in order to request the next page. Similar logic works for PREVIOUS recordsets.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    May 2006
    Posts
    5
    okay but please can you write for me the code, because I am still a beginner and this is my project. Thank you so much

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Are you doing your project for free? Because I generally charge something for my services...

    I encourage you to either hire a dba to help you, or learn advanced SQL real fast.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    May 2006
    Posts
    5
    sorry but I can't afford paying an advice

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You're not asking for advice. You are asking for somebody to do the work for you.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •