More On Custom Paging

Custom Paging across Group Headers

February 17, 2011

The purpose of this example is to demonstrate how to conduct paging across grouped headers within OpenWebStudio by taking advantage of Custom Paging within SQL Server. The original example queries for Custom Paging (seen here) take a basic idea of paging across a standard table where we always seen a specific number of records on the page. For example - Looking at every record in the LISTS table with 10 records on every page. However, that takes awhile, and it makes it so that sections of your data which should be grouped a broken up by the page breaks.

So the idea here is simple, what if we actually want to keep the records that are all in one group together on the same page?

The demonstration shows the Lists table, and uses grouped paging across the ListName values, allowing for only two ListName values per page. In this scenario, hundreds of records may be displayed, but evenly broken between pages properly based on the grouping mechanism. 

To do this, review the Custom Paging logic (http://openwebstudio.com/Community/Content/tabid/113/topic/Custom_Paging/Default.aspx).

Next, modify the query so that it is paging across the DISTINCT LISTNAMES rather than the full table.

 

Original paging example:

WITH ordered AS

(

SELECT *,

ROW_NUMBER()

OVER (order by [SORTTAG,YOUR_DEFAULT_SORT_COLUMN_NAME] ) as RowNumber

FROM YOUR_TABLE

)

SELECT

*

FROM

ordered

WHERE

RowNumber BETWEEN

(([PAGENUMBER,System]-1) * [PAGESIZE,System]) + 1

and

( [PAGENUMBER,System] * [PAGESIZE,System])

 

Select count(*) as TotalCount from YOUR_TABLE

 

Paging by Groups

To complete the task, we take the original query and alter it somewhat, instead of initializing the "ordered" table query to page over the full table, we force it to page over a sub query - Selecting the DISTINCT Group Column from the original table.

In our main query, instead of binding directly to "ordered", we bind to the original table and place the "ordered" query as a sub query joining where the Group Column in the main table matches the DISTINCT Group Column from the ordered table.

Finally, we return the COUNT of GROUP COLUMN records from the original table. As follows:

WITH ordered AS

(

SELECT GROUPCOLUMN,

ROW_NUMBER()

OVER (order by GROUPCOLUMN ) as RowNumber

FROM (Select distinct GROUPCOLUMN from YOUR_TABLE) alias

)

SELECT

* FROM YOUR_TABLE where GROUPCOLUMN in (

Select GROUPCOLUMN

FROM

ordered

WHERE

RowNumber BETWEEN

(([PAGENUMBER,System]-1) * [PAGESIZE,System]) + 1

and

( [PAGENUMBER,System] * [PAGESIZE,System])

)


Select count(distinct GROUPCOLUMN) as TotalCount from YOUR_TABLE

Thats it - the attached configuration demonstrates exactly what is suggested here, except it substitues "GROUPCOLUMN" with "ListName",  and YOUR_TABLE with "Lists".

Average ( Ratings):
 
Want to help out?
 
 

New York, NY • Baltimore, MD • Vienna, VA • St. Louis, MO • Seattle, WA • info@openwebstudio.com

Bookmark & Share Bookmark and Share