To add an additional counter to your MySQL query result set, you need to create a variable using the SET statement first.
Then, you need to increment the variable in your SELECT statement so that the variable value goes up for each row you have in the result set.
Let’s see an example of adding a counter. Suppose you have a countries table with the following data:
+------------------+
| name |
+------------------+
| England |
| Wales |
| Northern Ireland |
| Scotland |
+------------------+
First, you need to create a variable with the SET statement. Let’s name the variable as @counter as shown below:
SET @counter = 0;
The @counter variable above is initialized with the value 0.
Next, write a SELECT query that selects the name column and increments the value of @counter for each row as follows:
SELECT name,
@counter := @counter+1 AS row_counter
FROM countries;
Executing the query above will produce the following result set:
+------------------+-------------+
| name | row_counter |
+------------------+-------------+
| England | 1 |
| Wales | 2 |
| Northern Ireland | 3 |
| Scotland | 4 |
+------------------+-------------+
And that’s how you add a counter that increments with each row you have in the result set.
Keep in mind that the variable value in MySQL is saved until your current session ends.
If you run the same query again without initializing the @counter value to 0, the row_counter value will then start from 5 as shown below:
+------------------+-------------+
| name | row_counter |
+------------------+-------------+
| England | 5 |
| Wales | 6 |
| Northern Ireland | 7 |
| Scotland | 8 |
+------------------+-------------+
Since you need to use the variable value as the counter, you need to set the value back to 0 before running any other query.
The full code for setting a counter looks as follows:
SET @counter = 0;
SELECT name,
@counter := @counter+1 AS row_counter
FROM countries;
As you modify your SELECT statement to get the desired result, you need to set the counter back to 0 before running the query again.
Now you’ve learned how to include a counter in a MySQL SELECT statement. Nice work! 👍