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".