Get position of a row in a table, when ordered by name asc

I am displaying information from a table with pagination, say 30 rows per page, and order by name asc.

I need to know in which page a given row is, I have the id and name of that. Wondering if开发者_如何学Go that is possible.

Thanks guys.

Say the id is 'john'

select count(*)
from tbl
where name <= 'john'
order by name asc

Counting all the names, including 'john' gives you the position of john when sorted by name asc. Divide this by the rows-per-page and you will have the page number.

You can add a counter like this:

 set @i = 0; 
 select id, @i:=@i+1 as myrow from yourTableName

If you want to know where, for instance, id=130 is, you look to the linked "myrow". If that is 11, then it is on page 1. if it is on 35, it is on page 2, etc.