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
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;
INDEX() in MySQL for your required context.......perhaps in the futures plans ;) IF you find one, I am interested too.
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
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
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)
@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)
