[Edit]

MySQL - find row number

1 contributors

2 contributions

0 discussions

5 points

Created by:

WGates

412

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

Quick solution:

xxxxxxxxxx
 
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

Edit

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:

xxxxxxxxxx
 
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

Edit

create_tables.sql file:

xxxxxxxxxx
 
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:

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