Click here to Skip to main content
12,246,357 members (40,604 online)
Click here to Skip to main content
Add your own
alternative version

Stats

7.9K views
6 bookmarked
Posted

Pure SQL solution to Database Table Paging

, 15 Aug 2012 CPOL
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)

Share

About the Author

DrABELL
President Infosoft International Inc
United States United States
Dr. A. Bell is a full-stack software developer (Win/Web/Mobile). He holds PhD in EE/IT, published 300+ articles, authored 37 inventions and is credited for 10+ Enterprise level projects; currently focused on HTML5/CSS3, Javascript/jQuery, .NET/WPF/C#, Android/Angular.js, 'Big Data', AI, IoT. Alex participated in App Innovation Contests (AIC 2102/2013) with multiple winning submissions. Sample projects/pubs:
  1. Real-time NY Bus monitoring app (IoT)
  2. Semaphon™ semantic phone num-to-text converter
  3. Educational Web Portal
  4. Free Online NY Payroll Tax Calculator
  5. WebTV powered by Embedded YouTube Player (Goog #1 YouTube API for ASP.NET)
  6. Top-50 Digital Cameras (powered by iMark-DCAM rating engine)
  7. Pure CSS3 Slide Show
  8. Inflation Calculator
  9. CSS3 Modal Pop-up Dialog
  10. Multilingual Geocoder with Interactive Map
  11. Online Semantic Analyzer (Concordance Calculator)
  12. NY City Job Market and Agency Ratings
  13. Advanced CSS3 Table Formatting

You may also be interested in...

Comments and Discussions

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