MySQL: How to get row number order

These directions walk through how one can displaying the row number order/rank for result set obtained from executing a sql query. An example with walkthrough explanation is provided.

Let’s walk through an example.

First, let’s create an employee table in our db.

Query 1.1

mysql> create table employee (id smallint(5),
name varchar(15),salary smallint(5));

Next, insert records into the employee table.

Query 1.2

mysql> Insert into employee values (1,’jon’,1000);
mysql> Insert into employee values (2,’darvin’,3500);
mysql> Insert into employee values (3,’krik’,500);
mysql> Insert into employee values (4,’rook’,1500);
mysql> Insert into employee values (5,’alex’,100);

Now, display all records from employee table.

Query 1.3

mysql> select * from employee;
+------+--------+--------+
| id   | name   | salary |
+------+--------+--------+
|    1 | jon    |   1000 |
|    2 | darvin |   3500 |
|    3 | krik   |    500 |
|    4 | rook   |   1500 |
|    5 | alex   |    100 |
+------+--------+--------+
5 rows in set (0.00 sec)

Now, let’s retrieve all the records from employee table sorted by salary.

Query 1.4

mysql> select * from employee order by salary;
+------+--------+--------+
| id   | name   | salary |
+------+--------+--------+
|    5 | alex   |    100 |
|    3 | krik   |    500 |
|    1 | jon    |   1000 |
|    4 | rook   |   1500 |
|    2 | darvin |   3500 |
+------+--------+--------+
5 rows in set (0.00 sec)

Now, we really want to have an additional column which will run from starting at 1 (first row) to the last row. This is so we can display the row order in table. Here is the query.

mysql> set @row_num = 0;
SELECT @row_num := @row_num + 1 as row_number,id,name,salary FROM employee
ORDER BY salary;

+------------+------+--------+--------+
| row_number | id   | name   | salary |
+------------+------+--------+--------+
|          1 |    5 | alex   |    100 |
|          2 |    3 | krik   |    500 |
|          3 |    1 | jon    |   1000 |
|          4 |    4 | rook   |   1500 |
|          5 |    2 | darvin |   3500 |
+------------+------+--------+--------+
5 rows in set (0.00 sec)

Now you can see the additional column (row_number) which shows the row order. This is very useful while displaying the records.

Let’s dig a little deeper on how the above query 1.4 works to get row number order.

set @row_num = 0;

This Uses @row_num as user defined variable to hold the row count and is assigned a value 0.

SELECT @row_num := @row_num + 1 as row_number

Here, we increase the variable @row_num by 1, each time when the id column changes and we hold the value it had in previous row. This will keep incrementing the variable @row_num till it reach the last row in id column, using ‘row_number’ for column alias.