Calculate Page number

Question

Calculate Page number

rlouch

Valued Member

Points: 52
More actions
June 6, 2013 at 3:30 pm

#273621

I have a table that I need to return with a dynamically calculated page number. The number of items on a page are not the same but I do know where each page break is based on a column value being 0.
It would look something like this
ID, SomeTextValue, SomeOtherId, SortOrder
1, Hello, 98, 1
9, World, 4, 2
10, GoodBye, 5, 3
11, '', 0, 6 (This would be the page break)
12, It's a 98, 1
13, great day, 4, 2
I then would want to have a column with the page number of 1 for the first 3 rows and a page number of 2 for the last two rows, I really don't need the actual page break record to return in the dataset but I just need it to know where the page breaks are.
Right now I am using a loop to figure out what the page numbers are but I would like to speed this up if possible.
Thanks for any suggestions.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

rlouch (6/6/2013)
I have a table that I need to return with a dynamically calculated page number. The number of items on a page are not the same but I do know where each page break is based on a column value being 0.
It would look something like this
ID, SomeTextValue, SomeOtherId, SortOrder
1, Hello, 98, 1
9, World, 4, 2
10, GoodBye, 5, 3
11, '', 0, 6 (This would be the page break)
12, It's a 98, 1
13, great day, 4, 2
I then would want to have a column with the page number of 1 for the first 3 rows and a page number of 2 for the last two rows, I really don't need the actual page break record to return in the dataset but I just need it to know where the page breaks are.
Right now I am using a loop to figure out what the page numbers are but I would like to speed this up if possible.
Thanks for any suggestions.

So having a "0" in the SomeOtherID column determines a break. Do you want row 11 to be included on a page? If so, which one? I ask because the SortOrder numbering of "6" for that row seems a bit strange.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rlouch Valued Member Points: 52 More actions · Answer 2

Sorry, I got carried away with the copy and paste. Yes a 0 in the OtherId column stands for a page break, the sort order is exactly what it says and is sequential.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

This will do it including everything that your loop currently does AND solve the page number problem but MUCH faster. Details are in the comments in the code. I'm assuming you can add the PageNumber column to your table.

--=====================================================================================================================

-- Create some test data to work with.

-- Post your data this way on future posts and you'll get better help more quickly.

-- Please read the article at the first link in my signature for more info on this important point.

--=====================================================================================================================

--===== Create a test table. This is NOT a part of the solution

-- although the Clustered PK is.

CREATE TABLE dbo.YourTable

(

ID INT PRIMARY KEY CLUSTERED,

SomeTextValue VARCHAR(20),

SomeOtherID INT,

SortOrder INT,

PageNumber INT

)

;

--===== Populate the test table, This is NOT a part of the solution.

INSERT INTO dbo.YourTable

(ID,SomeTextValue, SomeOtherID)

SELECT 1,'Hello' ,98 UNION ALL

SELECT 9,'World' , 4 UNION ALL

SELECT 10,'GoodBye' , 5 UNION ALL

SELECT 11,'' , 0 UNION ALL

SELECT 12,'It''s a' ,98 UNION ALL

SELECT 13,'great day', 4

;

--=====================================================================================================================

-- This is an incredibly high speed solution to the problem.

-- SQL Server 2012 has a better, supported option but it's

-- still not as fast as this option.

--=====================================================================================================================

--===== Declare the necessary variables

DECLARE @Counter INT,

@SortOrder INT,

@PageNumber INT,

@PrevSomeOtherID INT,

@Dummy INT

;

--===== Preset some of the variables to the required starting values

SELECT @Counter = 1,

@PageNumber = 0,

@PrevSomeOtherID = 0

;

--===== Solve the problem with a "Quirky Update"

WITH

cteEnumerate AS

( --=== Sequentially number the rows of the table in order by ID

SELECT Counter = ROW_NUMBER() OVER (ORDER BY ID), --This is a "Safety Counter"

ID, SomeOtherID, SortOrder, PageNumber

FROM dbo.YourTable

) --=== This is a "Quirky Update" that works much like you'd do things in managed code.

-- The WITH and OPTION settings are extremely important to the success of the run.

-- The calculation with @Counter will force an error if anything ever goes awry.

-- The Clustered Index MUST be on the ID of the table in this case.

-- The @Dummy variable is a "booster" to help ensure success.

-- Leave any of those out and it will eventually fail.

-- This will work on a million row table in just a couple of seconds.

UPDATE tgt

SET @SortOrder = SortOrder = CASE WHEN @PrevSomeOtherID <> 0 THEN @SortOrder+1 ELSE 1 END,

@PageNumber = PageNumber = CASE WHEN @PrevSomeOtherID = 0 THEN @PageNumber+1 ELSE @PageNumber END,

@Counter = CASE WHEN Counter = @Counter THEN @Counter+1 ELSE 1/0 END,

@PrevSomeOtherID = SomeOtherID,

@Dummy = ID

FROM cteEnumerate tgt WITH (TABLOCKX, INDEX(1))

OPTION (MAXDOP 1)

;

--===== This just shows the results.

SELECT * FROM dbo.YourTable

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)