Languages
[Edit]
EN

MySql - get row position with SELECT query

4 points
Created by:
Root-ssh
115830

This article is focused about the problem how in MySql get row number with each row.

Quick solution:

SET @row_number := 0;

SELECT 
	(@row_number := @row_number + 1) AS `row_number`,
	t.*
FROM `my_table` t;

 

Practical example

Simple query:

SELECT 
	(@row_number := @row_number + 1) AS `row_number`,
	`client_id`,
	`creation_time`
FROM `clients`
JOIN (SELECT @row_number := 0) r

Warning: above query for some databases for the first query after the connection was established database may return NULL (e.g. MariaDB 10.3).

Screenshot:

row number with select query - HeidiSQL with MySql
row number with select query - HeidiSQL with MySql

Alternative solutions

1. Additional source

This approach creates a row number variable in FROM part.

SELECT 
	(@row_number := @row_number + 1) AS `row_number`,
	t.`client_id`,
	t.`creation_time`
FROM `clients` t, (SELECT @row_number := 0) r

Warning: above query for some databases for the first query after the connection was established database may return NULL (e.g. MariaDB 10.3).

2. Additional variable

This approach creates a row number variable outside SELECT query.

SET @row_number := 0;
SELECT 
	(@row_number := @row_number + 1) AS `row_number`,
	`client_id`,
	`creation_time`
FROM `clients`

3. Column partition

This approach uses a partition mechanism that lets to assign row numbers with a built-in function.

Using ROW_NUMBER() function with partition, we are able to assign unique row numbers per created partition. The partition can be created for any column. The partitioning mechanism tries to group rows that have the same values by indicated column and assign row numbers for rows in groups (each value creates a group that will have numbered rows from 1 to N). So, the trick is to create one global partition/group using an additional column that stores always the same value for each row - it will let to create one global group - in the below example, empty is the column name used to create one global partition/group. In the below case empty column contains NULL values always.

SELECT 
	ROW_NUMBER() over (PARTITION BY t.`empty`) AS `row_number`, /* required additional `empty` column */
	`client_id`,
	`creation_time`
FROM `clients` t

References

  1. MySQL - find row number 

MySQL - Problems

MySql - get row position with SELECT query
Native Advertising
ūüöÄ
Get your tech brand or product in front of software developers.
For more information Contact us
Dirask - we help you to
solve coding problems.
Ask question.

‚̧ԳŹūüíĽ ūüôā

Join