Row Numbers

Rows are stored in SQL databases in no particular order. Initially they will be stored in the order you insert them but once you start deleting rows the freed up space will be reused and the rows will then become more and more random. This generally isn't a problem as you can retrieve the rows in any order you desire simply by specifying an ORDER BY clause.

When you do apply an ORDER BY to your select statement you are then retrieving the records in the order specified. This is fine for when you want to retrieve only a small number of rows that match a reasonably specific WHERE clause but what about when you want to display thousands of rows but only a few at a time. MySQL provides a LIMIT clause to allow you to specify the rows from the resultset you want to fetch on this particular occasion but that is specific to that particular database engine. What about where you need it to work regardless of which SQL database is going to be used.

One simple solution to this is to set up a temporary table that has row numbers added that indicate their order after the appropriate ORDER BY has been applied. You can then specify minimum and maximum values for that row value as a part of the WHERE clause.

The following is an example of how to create a temporary table with row numbers that can then be used with the subsequent select calls to limit which rows are being retrieved without needing proprietary code such as limit.

SELECT @row_number:=@row_number+1 AS row_number,db_names FROM mysql_testing,
(SELECT @row_number:=0) AS t
ORDER BY db_names


This article written by Stephen Chapman, Felgall Pty Ltd.

go to top

FaceBook Follow
Twitter Follow