Giving a page number to a specific number of row in data

Question

Post reply

Giving a page number to a specific number of row in data

fredsql

SSC Rookie

Points: 33
More actions
April 1, 2009 at 9:12 am

#129016

Hi,
Has anyone had to split data into pages
e.g. for rows 1 to 25 I would like these to have a page number of 1
for rows 26 to 50 I would like these to have a page number of 2
and so on.
Can it be done in the data to that it can be used else where.
Thank you.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736280 More actions · Answer 1

http://www.sqlservercentral.com/articles/T-SQL/66030/

I assume you mean in querying. You can't determine which page in the server data is stored on.

Lowell SSC Guru Points: 323518 More actions · Answer 2

here's an example, assuming you are using row_number() function to decide how many rows:

you might need to fiddle with it,

declare @WhichPage int

Set @WhichPage = 3

SELECT * FROM (

select row_number() OVER(ORDER BY ID) AS RWNBR, * FROM SYSOBJECTS) MYALIAS

WHERE RWNBR BETWEEN (25 * (@WhichPage -1)) AND (25 * (@WhichPage -1)) + 25

--returns rows 75 thru 100 inclusive (26 rows)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

fredsql SSC Rookie Points: 33 More actions · Answer 3

I am actually looking to do something slightly different, I want to number data returned in a query the first 25 row would be numbered 1 the second 25 numbered 2 and so on.

Sorry for the confusion.

Thanks

Lowell SSC Guru Points: 323518 More actions · Answer 4

fredsql (4/2/2009)
I am actually looking to do something slightly different, I want to number data returned in a query the first 25 row would be numbered 1 the second 25 numbered 2 and so on.
Sorry for the confusion.
Thanks

If you still want paging,

just select the variable @WhichPage then, and never display the rownumber:

declare @WhichPage int

Set @WhichPage = 3

SELECT @WhichPage ,* FROM (

select row_number() OVER(ORDER BY ID) AS RWNBR, * FROM SYSOBJECTS) MYALIAS

WHERE RWNBR BETWEEN (25 * (@WhichPage -1)) AND (25 * (@WhichPage -1)) + 25

---returns rows 75 thru 100 inclusive (26 rows)

if you jusst want them to have a variable, soemthing like this will do it:

SELECT

(RWNBR/25) + 1 As PageNumber,* FROM (

select row_number() OVER(ORDER BY ID) AS RWNBR, * FROM SYSOBJECTS) MYALIAS

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

fredsql SSC Rookie Points: 33 More actions · Answer 5

Thank you,

I had worked out that Row_Number could help me and did something nearly exactly the same

(ROW_NUMBER() OVER (PARTITION BY a.Col1 ORDER BY a.Col2 DESC)-1) / 25

Only I have -1 so I get 25 rows for each section.

The new Row_Number, Rank and Dense_Rank functions are coming in very handy.

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 6

Hi,

No Idea why do you want to do that way. Going forward you will have face issues. Lets haev the Numbers to the rows in the query and control how many records you want to display in UI. Don't hard code the page numbers in the data that is comming from SQL.

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers