Languages
[Edit]
EN

MySql - get row position with SELECT query

4 points
Created by:
Root-ssh
98350

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

Quick solution:

SELECT 
	(@row_number := @row_number + 1) AS `row_number`,
	t.*
FROM `my_table_name` t
JOIN (SELECT @row_number := 0) r

 

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

Screenshot:

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

Alternative solution

This approach creates variable outside query.

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

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