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.
