Results 1 to 4 of 4
  1. #1
    Join Date
    May 2005
    Posts
    23

    Unanswered: retrieving specific page(number of rows) form table

    hi,

    i need SP that receive 2 integers ,@NUM_ROWS and @PAGE_NUMBER,
    and return the rows in that page.
    for example:

    SP(4,2) will return 4 rows in page number 2 .

    So if i have table with 9 rows i will get rows 5-8,
    the first page is rows 1-4 the second page is 5-8 and the 3 page is row 9.

    i have to assume that rows can be deleted form that table.
    thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You have to have a (preferably unique) column or set of columns to order the data consistently each call. Do you have an incrementing identity field or datetime stamp?
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    May 2005
    Posts
    23
    i have the PK of the table, but i have to assume some records have been deleted.
    so i can not assume i have perfectly order column

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Not necessary.
    Code:
    declare	@NUM_ROWS int
    declare	@PAGE_NUMBER int
    
    select	[YourTable].*
    from	[YourTable]
    	inner join --PageRows
    		(select	[PKey],
    			count(*) as RowNum
    		from	[YourTable]
    			inner join [YourTable] Ordinal on [YourTable].[PKey] >= Ordinal.[PKey]
    		having	count(*) between (@PAGE_NUMBER * @NUM_ROWS) + 1 and (@PAGE_NUMBER + 1) * @NUM_ROWS) PageRows
    		on [YourTable].[Pkey] = PageRows.Pkey
    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
  •