How is row_number used in mysql?

1 year ago
Emily Johnson
1 minute

Even though there is no built-in ROW_NUMBER function in MySQL, a similar functionality can be achieved by using variables.

Imagine we have the following example table:

+----+-------+
| id | name  |
+----+-------+
| 1  | John  |
| 2  | Alice |
| 3  | Bob   |
+----+-------+

If we want to add an increasing line number to each line, we can achieve this by using a variable.

SELECT @row_number:=@row_number+1 AS row_number, id, name
FROM table_name, (SELECT @row_number:=0) AS t
ORDER BY id;

This will result in the following:

+------------+----+-------+
| row_number | id | name  |
+------------+----+-------+
| 1          | 1  | John  |
| 2          | 2  | Alice |
| 3          | 3  | Bob   |
+------------+----+-------+

In the above query, we utilized a variable @row_number and incremented its value within the SELECT statement. It was then returned as a new column “row_number”. Please note that we also used a subquery (SELECT @row_number:=0) AS t to initialize the variable’s value to 0.

This way, we can add an increasing line number to each line.

bannerAds