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);