Pure SQL solution to Database Table Paging

Introduction

Database table paging is one of the most common development tasks. There are plenty of solutions spread over Internet techno sphere, which came in a different flavor in terms of programming dialects, performance and simplicity/flexibility. Suggested solution demonstrates pure SQL solution encapsulated in a single SQL statement, which provides universal portability, simplicity and highly-optimized performance by utilizing the powerful sorting features of Database engine itself without need for any additional programming resources.

Background

Problem definition and solution: Assuming the Database Table "TBL_ITEM" containing two columns (fields) of interest: “Item” column, representing the unique ID and “Rank”, which is used for sorting in ascending order, the general paging problem is specified mathematically as following: Select N-rows from the table ordered by Rank offsetting, in other words, skipping (M-N) rows. In practical example, N=10 (rows selected) and M=100 (offset = M-N). SQL solution to this problem is general form is shown in Listing 1, followed by practical example in Listing 2

Using the code 

Listing 1

SELECT TOP N Item, Rank FROM (SELECT TOP M Rank, Item FROM TBL_ITEM ORDER BY Rank)  AS [SUB_TAB] ORDER BY Rank DESC 

Listing 2 

SELECT TOP 10 Item, Rank FROM (SELECT TOP 100 Rank, Item FROM TBL_ITEM ORDER BY Rank) AS [SUB_TAB] ORDER BY Rank DESC 

The logic behind the solution is simple: it essentially tells SQL engine to select Top M Rows ordered originally, then reverse the sorting direction and select Top N rows, so in example shown in Listing 2 it will return rows from 91 to 100.

Additional sorting 

Both solutions described above are based on query-subquery SQL technique and do the job exactly as described, though the resulting row set is ordered in reverse direction. If keeping the original sorting direction is required, then simple extension provides another universal solution that satisfy this condition (see Listing 3 containing corresponding SQL statement):

Listing 3 

SELECT TAB.Rank, TAB.Item FROM (SELECT TOP 10 Item, Rank FROM (SELECT TOP 100 Rank, Item FROM TBL_ITEM ORDER BY Rank)  AS [SUB_TAB] ORDER BY Rank DESC)  AS TAB ORDER BY TAB.Rank;

Multiple Pages

Solutions described above produce single page of N-rows offsetting (M-N) rows. Unfortunately, standard SQL implemented by most popular Database engines does not allow to pass variable to its Top clause. Therefore, in order to generate multiple pages developer should apply additional technique, for example, programmatically modifying SQL statement from the client application (e.g., using SQLDataSource or SQLDataReader objects included in .NET Library)

Union SQL Query to combine multiple Pages

Union Query can be used to combine multiple pages into a single result set as shown in Listing 4:

Listing 4

SELECT TOP 20 Item, Rank FROM (SELECT TOP 100 Rank, Item FROM TBL_ITEM ORDER BY Rank)  AS SUB_TAB ORDER BY Rank DESC UNION ALL SELECT TOP 10 Item, Rank FROM (SELECT TOP 300 Rank, Item FROM TBL_ITEM ORDER BY Rank) AS SUB_TAB ORDER BY Rank DESC 

Resulting record set contains 20 rows starting from 81st row and additional 10 rows starting from row 291 in original Table "TBL_ITEM" sorted by Rank.

Note: UNION ALL predicate used in sample query shown above does not check for duplicates and returns all records as specified in its "SELECT" sub-queries. In order to select only distinct rows use UNION predicate instead of UNION ALL.

Points of Interest 

Described technique was implemented in popular online Video Player with content updated daily, running on the Database containing about 5000 items, and demonstrated high-performance and flawless execution. Relevant to mention, that “inner” sub-query could implement any type of “WHERE” clause in the same way as any usual SQL select statement.

History 

Last updated: 08/15/2012