How to get the position of a row in an arbitrary ordering?

I'm paginating my SQL results (10 per page). I want to find out on which page a given row (id known via URL) would be, so that I can jump to the correct page directly.

The ordering is known, but it's not a simple ORDER BY id ASC, so I can't just count the rows id < wanted_id.

MySQL-specific commands and subqueries are permitted. I want to avoid variables.

Any approaches?

EDIT to make myself clear...

I am capable of getting the results. What I need is the rank (i.e. the number of 开发者_运维百科rows that would be before that row) of that row. I'm not looking for brute-force approaches (I would rather retrieve 10k ids and find that rank programmatically).

Perform the query and insert the result set into a temporary table. The temp should have, among other things, a dedicated auto increment column.

Find the value of the auto-increment column where the row contains the id in question, perform some simple arithmetics to calculate the page the row belongs to, and give out the page.

Use a second Query SELECT COUNT(*) FROM ... WHERE .... In the WHERE clause, narrow the selected records down to the ones that come before the record in question.

select *, 
_rowid, 
floor (_rowid / 10)  
from table

Sorry, missed the "no variables" requirement.

You can use "limit" to jump to the specific page.

select count(*) as rank_number
from my_table
order by c1, c2 desc
where 
    c1 <= (select c1 from my_table where id = @id)
    and
    c2 >= (select c2 from my_table where id = @id)