Finding the position of a record in MySQL

Added on: Saturday 4th July 2009

Normally when retrieving records from a MySQL database you loop through the results and perform an operation on each in time. The other day, however, I simply wanted to get the position of a certain record in a set - nothing more.

Many people say that the actual position in a result set is meaningless because it depends on the sort order and other factors.

To a certain extent this is true but consider this scenario:

Our CRM application allows you to scroll through groups of records and keeps track of which record you are on even if you go to other pages and then come back.

It also features a navigation bar to allow you to move forward or backward through the set.

As it uses session variables these will be cleared once you've logged out and rather than using database storage I just wanted a quick way to jump to a certain record in the set.

A dropdown list of all records in the set works fine for small sets but the overhead of loading this is unacceptable for very large sets.

Instead I wanted users to be able to find a record in the database and then be able to jump to scroll mode at the position of that record in the set.

I could have simply pulled all the IDs from the set and then looped through them in php until a match is found for the current record but I was sure there was a way to get the position of a record using MySQL alone.

It turns out there is and the code is something like that shown below:

SET @C=0;
SELECT C FROM (SELECT @C := @C +1 AS C, [recordID] FROM [sqlStatement]) X WHERE [recordID] = Y;

Where C is your counter, [sqlStatement] is the SQL to retrieve the IDs (ie [recordID]) in the order that you want them and Y is the ID of the record you want the position of.

Note that this requires two calls to MySQL so if you are using PHP you'll find you have to make them separately as mysql_query doesn't support multiple queries.

Permalink