Tip/Trick

# Pure SQL solution to Database Table Paging

, 15 Aug 2012 CPOL
 Rate this:
Portable solution encapsulated in single SQL query allows to select N rows offsetting M records

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

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

## Share

President Infosoft International Inc
United States
Dr. A. Bell has 20+ years of Software and Electrical Engineering experience: Win/Web veteran, published 300+ articles and authored 37 inventions, credited for 10+ Enterprise level projects (>250k code lines); currently focused on .NET/WPF, C#, HTML5, jQuery, SQL, 'Big Data', AI, Speech Tech and Mobile apps. He participated in App Innovation Contest (AIC 2102/2013) with several winning submissions. Sample projects/pubs follow: