mysql 排序位置,mysql排序表并获取行位置

I've got a problem in mysql, I have a table of ID and nubmer ... somthing like that

ID -------- number

3 -------- 340

1 -------- 10

12 -------- 23

And now I would like to selecet by ID, let's say ID=3 and is there a way of saying what is the position of this row in sorted table? In this case, ID = 3 would have first position, cuase of highest value in number. ID=12 would have second position ... and so on.

解决方案set @row_number:=0;

select * from

(select ID, @row_number:=@row_number+1 from your_table order by number desc)

as row_to_return

where ID=3;

The above query can be change to replace ID to anything you need.

However, for simple usage, ORDER BY number DESC is better and optimized.