Get the index of mysql result raw

Question

I have the following table:-

id | name
 1 | abc
 5 | def
 9 | ghi
 10 | jkl
 11 | mno
 12 | pqr

I want to get the index of a given id

example:-

SELECT `id`, INDEX FROM `table` WHERE `id`>5
// INDEX is the function that I want

result:-

id | INDEX
 9 | 3
 10 | 4
 11 | 5
 12 | 6

I googled it and not found a answer.

Thanks in advance for nay help.

Answer 1

You could get this by

select max(id) id,count(*)
from demo
where id < 9;

If id column is set to auto increment

Demo

For multiple rows you can do a self left join

select a.id,count(*)
from demo a
left join demo b on a.id >= b.id
group by a.id
having a.id > 5

Demo

Answer 2

SELECT id, (select count(1) from table where id<9) FROM table WHERE id=9;