CODEWITHSUNDEEP
mysqlsql-order-by
Martin
Martin

Reputation: 21

mysql sort table and get row position

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.

Upvotes: 1

Views: 3458

Answers (3)

SW4
SW4

Reputation: 71170

If you're returning the resultset via PHP, you can use PHP to determine the position of the value '3' within the array for the ID field by using array_search

Or change your SQL as follows:

select
@rownumber:=1+@rownumber as 'rownumber',
id_field, number_field
from yourtable, (SELECT @rownumber:=0) rownumber order by number_field DESC

This will add an additional column 'rownumber' which provides the number of each row

Upvotes: 0

ajreal
ajreal

Reputation: 47321

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.

Upvotes: 1

Chandresh M
Chandresh M

Reputation: 3828

try this query if usefull.

SELECT ID,number FROM table_name ORDER BY number DESC;

Thanks.

Upvotes: 0

Related Questions