Paging Through a Result Set using Row Number
Language(s):MySQL
Category(s):Paging, Row Number, Stored Procedures, Subquery
This article shows how to use a Row ID to page through a MySQL query. This is especially useful for using with a web page, where you may want to break up a query into a specific number of rows per page.

Paging by Row Number

Suppose you have a query that returns a large number of rows, but you only want to display the results one page at a time. This article shows you how to write a MySQL query that will allow you to select any portion of the result by row number.

First of all, let's create a sample table to demonstrate the idea. Let's use a simplified table to store article titles similar to the Articles table used with Skycoder.com. Please note that this is a non-normalized table. In a real world example, you would want to have a foreign key to an Authors table, rather than store it in the Articles table, but let's keep it simple for this example:

Drop Table If Exists ArticlesEx

 

Create Table ArticlesEx

(

     ID                   Int Not Null

                          Auto_increment 

                           Primary Key,

     ArticleTitle         varChar(256),

                Index(ArticleTitle),

     Author               varChar(50),

                Index(Author),

     ArticleDate          TimeStamp Not Null Default Now()

);

 

If you would like to enter some test data for using with this example, here is a simple query that will enter unique values for each title:

 

Insert Into ArticlesEx

(ArticleTitle, Author)

Values(Concat("Article_", Cast(Last_Insert_ID() as Char))

, "Joe Blow")

               

Run that guy couple of dozen times and you will have plenty of records to mess with.

Returning a Row ID

Although there is a unique ID in this table that serves as the Primary Key, we don't want to use this as a row number for several reasons. First of all, we probably are going to want to display the results in a different order than the Primary Key. Suppose we want to order them by Author Name and then by Title. We can't rely on the Primary Key to be in the correct order. Another problem is that we may have gaps in the Primary Key values where a row has been deleted. Well, it turns out there is an easy solution to this problem. We can just use a variable that gets initialized to zero and then incremented with each row. Here is an example of what I'm talking about:

Set @RowNumber = 0;

Select @RowNumber := @RowNumber + 1 as RowNumber,

      ArticleTitle, Author, ArticleDate

From ArticlesEx

Order By Author, ArticleTitle

 

The query above will produce results something like the following:

RowNumber    ArticleTitle      Author             ArticleDate                 

1                      Article_0         Joe Blow          2008-12-23 21:20:07

2                      Article_1         Joe Blow          2008-12-23 21:20:22

3                      Article_10       Joe Blow          2008-12-23 21:20:24

4                      Article_11       Joe Blow          2008-12-23 21:20:24

5                      Article_12       Joe Blow          2008-12-23 21:20:24

6                      Article_13       Joe Blow          2008-12-23 21:20:25

7                      Article_14       Joe Blow          2008-12-23 21:20:25

8                      Article_15       Joe Blow          2008-12-23 21:20:25

9                      Article_16       Joe Blow          2008-12-23 21:20:25

10                    Article_17       Joe Blow          2008-12-23 21:20:25

.

                             .

.

 

Ok - looks good so far - but there is a problem if we try to use the RowNumber in the Where clause. It WILL NOT work to do the following:

Set @RowNumber = 0;

Select @RowNumber := @RowNumber + 1 as RowNumber,

      ArticleTitle, Author, ArticleDate

From ArticlesEx

Where @RowNumber Between 5 And 10

Order By Author, ArticleTitle

 

The problem is that since @RowNumber is initially set to zero, there will be no records that match the Where clause when it is evaluated.

Well, it's impossible to defeat a techie - so let's figure out a solution. What we want is for the RowNumber to be evaluated prior to the Where clause. Any ideas? How about a sub-query!

Let's rewrite the query above as a sub query, aliased as a table and select the columns from the sub-query. So, now the query will look like this:

Set @RowNumber = 0;

Select RowNumber, ArticleTitle, Author, ArticleDate

From

     (

     Select @RowNumber := @RowNumber + 1 as RowNumber,

            ArticleTitle, Author, ArticleDate

     From ArticlesEx

     Order By Author, ArticleTitle

     ) As SubQuery

Where RowNumber Between 5 and 10;

Now we get the desired results:

5          Article_12       Joe Blow          2008-12-23 21:20:24

6          Article_13       Joe Blow          2008-12-23 21:20:25

7          Article_14       Joe Blow          2008-12-23 21:20:25

8          Article_15       Joe Blow          2008-12-23 21:20:25

9          Article_16       Joe Blow          2008-12-23 21:20:25

10        Article_17       Joe Blow          2008-12-23 21:20:25

 

It is important to note that the Order By clause MUST be placed in the sub-query, otherwise the RowNumbers will not be in contiguous order. (If this doesn't make sense - try it and you will see what I mean).

Putting it in a Stored Procedure

Now that we have the proof of concept worked out, let's finish the job. If you have been putting SQL statements in your HLL code - stop. Instead create a stored procedure. Here is a stored procedure that will accepts From and To parameters and returns the result set accordingly:

 

 

 

Delimiter $$

Drop Procedure If Exists sp_ArticlesEX$$

 

Create Procedure sp_ArticlesEX(in FromRow int, in ToRow int)

  Begin

    Set @RowNumber = 0;

    Select RowNumber, ArticleTitle, Author, ArticleDate

    From  

     (

     Select @RowNumber := @RowNumber + 1 as RowNumber,

            ArticleTitle, Author, ArticleDate

     From ArticlesEx

     Order By Author, ArticleTitle

     ) As SubQuery

    Where RowNumber Between FromRow and ToRow;

  End$$

Delimiter ;

 

You can compile the procedure (in dos at least) as follows:

>mysql yourdatabase -uyourusername -pyourpassword < StoredProc.sql

Where you want to substitute yourdatabase, yourusername and yourpassword for the correct values on your system.

To call the stored procedure, use the Call statement as shown below:

Call sp_ArticlesEX(5, 10);

This article has been viewed 0 times.
The examples on this page are presented "as is". They may be used in code as long as credit is given to the original author. Contents of this page may not be reproduced or published in any other manner what so ever without written permission from Idioma Software Inc.