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.

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.

Leave a Reply