Get special record position in records

Hi
I execute this query in database
SELECT * FROM tbl_user ORDER BY sScore
and result is
Username sScore
1 jack 100
2 Sara 59
3 frank 20
4 safar 10
5 omid 5
6 asghar 2

I need to know that what is grade of Sara between in all user
Also i need to know what is position of Sara in list (Sara position is 2

It's impossible that the query gives this result, unless you add "DESC" to the ORDER BY clause.

Convert the result to an array. Then do an array_search for "Sara". It will give you the array key. Because arrays are zero-based, add 1 to the key to determine Sara's position.

@omid_student

If you need for some special reason the returned records to be enumerated by the database server, see this article: http://www.fromdual.com/ranking-mysql-results

On a random table of mine I tested the suggestion, it works:

Code:

$this->db->query("SET @rank = 0;");
        $result = $this->db->query(
"
    SELECT @rank := @rank + 1 AS rank, name, id
    FROM social_networks
    ORDER BY id DESC;
"
            )->result_array();
        var_dump($result);

It's impossible that the query gives this result, unless you add "DESC" to the ORDER BY clause.

Convert the result to an array. Then do an array_search for "Sara". It will give you the array key. Because arrays are zero-based, add 1 to the key to determine Sara's position.

I used this way but i thought it is wrong way
Thanks

It's impossible that the query gives this result, unless you add "DESC" to the ORDER BY clause.

Convert the result to an array. Then do an array_search for "Sara". It will give you the array key. Because arrays are zero-based, add 1 to the key to determine Sara's position.

Hey do you can help about below result?
I need define level for each user with his score
Example if user's score is 25 so level is 6
Thanks
Level 1: 0
Level 2: 5
Level 3: 10
Level 4: 15
Level 5: 20
Level 6: 25
Level 7: 30
Level 8: 35
Level 9: 40
Level 10: 45

I would store the ranking in the table while saving the record. Otherwise, you need to add the ranking attribute to the records afterwards (after getting the results).
In both cases, I would use  a function like this (in the controller):

PHP Code:

private function get_rank($score) 
{
  return floor($score / 5) + 1;
} 


To get a ranking:

PHP Code:

$r = $this->get_rank($score); 


I don't know if this can be done in MySQL directly.

I would store the ranking in the table while saving the record. Otherwise, you need to add the ranking attribute to the records afterwards (after getting the results).
In both cases, I would use  a function like this (in the controller):

PHP Code:

private function get_rank($score) 
{
  return floor($score / 5) + 1;
} 


To get a ranking:

PHP Code:

$r = $this->get_rank($score); 


I don't know if this can be done in MySQL directly.

Yes it is best solution Thank you