Click here to Skip to main content
Click here to Skip to main content

Pure SQL solution to Database Table Paging

, 15 Aug 2012
Rate this:
Please Sign up or sign in to vote.
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.

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.

Reference 

YouTube Video Player (daily updated page)

History 

Last updated: 08/15/2012

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

DrABELL
President Infosoft International Inc
United States United States
Dr. A. Bell has 20+ years of Software and Electrical Engineering experience. He is Win/Web veteran, published 200+ articles and authored 37 inventions, currently focused on: Windows 7/8, HTML5, CSS3, jQuery, SQL, .NET, ASP.NET, WPF, C#, Speech Technology and Mobile apps. He's been among recent App submission winners (The Windows 8* & Ultrabook™ App Innovation Contest 2012). Sample pubs:
  1. Edumatter M12: School Math Calculators and Equation Solvers (contest winner)
  2. Engineering Calculator VOLTA-2013 (contest winner)
  3. HTML5 Best Practices: Table formatting via CSS3
  4. Edumatter-M12 for Windows, app overview
  5. Engineering Calculator VOLTA-814D
  6. CoolPhone: phone numbers-to-text converter
  7. SQL generates large data sequence
  8. Aggregate Product function extends SQL
  9. Top-50 Digital Cameras
  10. WebTV Project: Embedded YouTube Player (Goog #1 YouTube API for ASP.NET)
Dr. Bell is personally credited for 10+ Enterprise level projects (Finance/Investment, Engineering, Edu) w/total code base exceeding 250k lines; doing consulting in NYC for 20 yrs.
Follow on   Twitter

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 15 Aug 2012
Article Copyright 2012 by DrABELL
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid