How to add ROW INDEX as a column to SQL SELECT query?

Question

Assume I've SQL query like this:

SELECT id, name, index(not a real column) FROM users ORDER BY rating DESC

I want to add column to selected columns that will represent the index of the record.

Example:

 id    name  rating
 1     a     4
 2     b     2
 3     c     8
 4     d     5

For this table I want to get:

 id    name  rating  index
 3     c     8       1
 4     d     5       2
 1     a     4       3
 2     b     2       4

Isn't something missing in the sentence that you briefly describe your requirement? "add column to selected columns..." NVM I got what you mean. You need row index number of the resulting table. — bonCodigo, Nov 25 '12 at 11:56
Yes. (Apparently you can't answer yes or no, because there are a minimum of characters on this textarea) — Yarin Gold, Nov 25 '12 at 11:58
Possible duplicate of [MySQL - Get row number on select](https://stackoverflow.com/questions/2520357/mysql-get-row-number-on-select) — Breeze, Feb 23 '18 at 10:54

score 25 · Accepted Answer · answered Nov 25 '12 at 12:03

25

Try the following to get the row_index:

set @row_num = 0; 
SELECT id,name,rating, @row_num := @row_num + 1 as row_index FROM users
ORDER BY rating desc;

answered Nov 25 '12 at 12:03

bonCodigo

14,268
1
48
91

1

It worked, but seems that there is not elegant solution for that. – Yarin Gold Nov 25 '12 at 12:19
How elegant you are looking for Yarin? :) I am also wondering what you meant by the "minimum of characters on this textarea"... – bonCodigo Nov 25 '12 at 12:24
I was looking for something like INDEX() function. As for the 'minimum' thing, it's relates to Stackoverflow validation on textarea when adding comments :P – Yarin Gold Nov 25 '12 at 13:03
Good one. :D on 'minimum note' . BTW usage of `INDEX()` in MySQL for your required context.......perhaps in the futures plans ;) IF you find one, I am interested too. – bonCodigo Nov 25 '12 at 13:09
2

MySQL does not support analytic/"windowing"/OLAP-type functions, so this is as elegant as it gets! In Oracle or MS SQL-Server you could write: select id, name, rating, row_number () over (partition by id order by rating desc) "index" from users order by rating desc – Lord Peter Nov 25 '12 at 15:03
But notice, this code is applied for native sql code. If you want to call a query in php, you should declare it as following: – Nguyen Tan Dat Jun 12 '18 at 04:08

score 3 · Answer 2 · answered Dec 02 '19 at 08:19

3

It's now builtin in MySQL 8.0 and MariaDB 10.2:

SELECT
  id, name, rating,
  ROW_NUMBER(ORDER BY rating DESC) AS index
FROM users ORDER BY rating DESC

answered Dec 02 '19 at 08:19

caram

1,494
13
21

score 1 · Answer 3 · answered Nov 25 '12 at 12:02

1

Something like set @cnt=0; select *, @cnt:=@cnt+1 from users order by rating desc;

Should do the job (You'll need to set @cnt before each query though)

answered Nov 25 '12 at 12:02

nvlass

665
1
6
15

score 1 · Answer 4 · answered Dec 17 '19 at 11:00

1

If you don't have a column to order by, you can still use ROW_NUMBER():

SELECT 
  id, name, rating,
  ROW_NUMBER(ORDER BY (select 1)) AS index
FROM users

answered Dec 17 '19 at 11:00

Roman Gudkov

3,503
2
20
20

Nguyen Tan Dat · Answer 5 · 2018-07-17T08:40:26.963

@bonCodigo's answer is correct when you try in php admin or in sql code. But if you want to call a query in php, you should declare it as follows:

$sql_query = "SELECT (@row_number:=@row_number + 1) AS 'row_index', id, name, rating FROM (SELECT @row_number:=0) AS temp, users"

$this->db->query ( $sql_query )->result ();

(This piece of code is for Code igniter framework)

UPDATED:

However, it won't work when using JOIN together. In this case, you need to read data as usual then add a column index by yourself, you can take an idea from this code:

$list = $this->db->query ( $sql_query )->result ();

for($i = 0; $i < count($list); $i++ ){
    $list[$i]->no = $i + 1;
}

(This piece of code is for Code igniter framework)

How to add ROW INDEX as a column to SQL SELECT query?

5 Answers5

Linked