CODEWITHSUNDEEP
phpmysql
Jeremy Roy
Jeremy Roy

Reputation: 1291

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 that is possible.

Thanks guys.

Upvotes: 1

Views: 253

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107766

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.

Upvotes: 3

Nanne
Nanne

Reputation: 64419

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.

Upvotes: 1

Related Questions