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

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

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

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)

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;

Related