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;");
(02-24-2018, 12:21 PM)Wouter60 Wrote: It's impossible that the query gives this result, unless you add "DESC" to the ORDER BY clause. I used this way but i thought it is wrong way Thanks
(02-24-2018, 12:21 PM)Wouter60 Wrote: It's impossible that the query gives this result, unless you add "DESC" to the ORDER BY clause. 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) To get a ranking: PHP Code: $r = $this->get_rank($score); I don't know if this can be done in MySQL directly.
(02-25-2018, 12:59 AM)Wouter60 Wrote: 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).Yes it is best solution Thank you |
Welcome Guest, Not a member yet? Register Sign In |