Listing records with a running count

This request came to a programming forum: "how do I list a record set with a running count for each record?"

There are several answers to this question. The first that would come to mind should be to implement the count within the application at the receiving end of the data stream. This solution, though, requires different implementation details foe each language, and could not be what we are looking for.

Instead, there is a quick way of making MySQL count records while listing them, so that we have a result that is always the same, no matter which language we use to code our application.

Perhaps not everybody is aware that MySQL allows so called "user variables" in your queries. They are private variables for each connection, and they stay available for the whole duration of your session.

For example, a frequent usage of this feature was a workaround for lack of subqueries in MySQL versions lower than 4.1:

mysql> SELECT @maximum := max(salary) from wages;

mysql> SELECT name, surname, salary from wages where salary = @maximum;

In the same vein, we can use a user variable to create a running counter. Since each variable holds a NULL value unless explicitly initialized, you need to send two queries to get what you want.

First, let's see the query without a counter.

mysql> SELECT name,salary, gender FROM persons;
+--------+--------+--------+
| name   | salary | gender |
+--------+--------+--------+
| John   |   5000 | m      |
| Mario  |   6000 | m      |
| Frank  |   5000 | m      |
| Otto   |   6000 | m      |
| Susan  |   5500 | f      |
| Martin |   5500 | m      |
| Mary   |   5500 | f      |
| Bill   |   5000 | m      |
| June   |   6000 | f      |
+--------+--------+--------+
9 rows in set (0.01 sec)

Thyen, let's execute those two queries we have mentioned. First of all, we need to initialize the counter.

mysql> set @N = 0;
Query OK, 0 rows affected (0.27 sec)

Thus, we can send the second query, including our counter, with an appropriate formula to increment it.

mysql> SELECT @N := @N +1 AS number, name,salary,gender FROM persons;
+--------+--------+-----------+--------+
| number | name   |    salary | gender |
+--------+--------+-----------+--------+
|      1 | John   |      5000 | m      |
|      2 | Mario  |      6000 | m      |
|      3 | Frank  |      5000 | m      |
|      4 | Otto   |      6000 | m      |
|      5 | Susan  |      5500 | f      |
|      6 | Martin |      5500 | m      |
|      7 | Mary   |      5500 | f      |
|      8 | Bill   |      5000 | m      |
|      9 | June   |      6000 | f      |
+--------+--------+-----------+--------+
9 rows in set (0.01 sec)

Please note that each variable in a query can be calculated only once per record.

Finally, if you adopt such a solution, don't forget to initialize the counter before running the query a second time, otherwise your records will hold a constantly increasing counter!