MySQL - find row number

In this article, we would like to show how to find row number in MySQL.

Quick solution:

SET @row_number := 0;

SELECT w.`row_number`
FROM (
	SELECT 
		(@row_number:=@row_number + 1) AS `row_number`,
		t.*
	FROM `table_name` t
) w
WHERE
	w.column_name='column_value';

Practical example

In the below example, we try to find row number where Simon Colins is placed - row number depends on internal SELECT query.

Example input:

Example query:

SET @row_number := 0;

SELECT w.`row_number`
FROM (
	SELECT 
		(@row_number:=@row_number + 1) AS `row_number`,
		t.*
	FROM `users` t
) w
WHERE
	w.`name`='Simon' AND w.`surname`='Collins'

Example result:

Database preparation

create_tables.sql file:

CREATE TABLE `users` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NOT NULL,
	`surname` VARCHAR(50) NOT NULL,
	PRIMARY KEY (`id`)
);

insert_data.sql file:

INSERT INTO `users`
	( `name`, `surname`)
VALUES
	('John', 'Stewart'),
	('Chris', 'Brown'),
	('Kate', 'Lewis'),
	('Ailisa', 'Gomez'),
	('Gwendolyn', 'James'),
	('Simon', 'Collins'),
	('Taylor', 'Martin'),
	('Andrew', 'Thompson');

References

1. MySql - get row position with SELECT query