bululu Posted November 1, 2013 Share Posted November 1, 2013 I have the following array: $learners=array('Eliza'=87, 'Joe'=81, 'Anne'=69, 'Marley'=39, 'Teddy'=39, 'Jemma'=90, 'Sylvia'=87); So far I have been able to separate the two arrays as follows: $tudents=array_keys($learners); $scores=array_values($learners); The ranking is as follows: Student Score Position Jemma 90 1 Sylvia 87 2 Eliza 87 2 Joe 81 4 Anne 69 5 Marley 39 7 Teddy 69 7 I would like to create a new array with names as keys and positions as values i.e $positions=array('Jemma'=1, 'Sylvia'=2, 'Eliza'=2, 'Joe'=4, 'Anne'=5, 'Marley'=7, 'Teddy'=7); This will allow me to echo any name and position at any point on the script. The ranking is not straightforward if the scores have duplicates. If there is a tie at number 2, the 3rd position is skipped. If the tie occurs at the end of the scores, then both scores will be placed at the last position and the preceding position will be skipped, in the example above, position 6 has been skipped and the two 39s occupy position 7. Any help will be appreciated Quote Link to comment Share on other sites More sharing options...
Barand Posted November 1, 2013 Share Posted November 1, 2013 No need to split the array $learners=array( 'Eliza' => 87, 'Joe' => 81, 'Anne' => 69, 'Marley' => 39, 'Teddy' => 39, 'Jemma' => 90, 'Sylvia' => 87 ); arsort($learners); $ranking = array(); $k = $prev = 0; $rank = 1; foreach ($learners as $name => $score) { $k++; $rank = $score == $prev ? $rank : $k; $ranking[$name] = $rank; $prev = $score; } echo '<pre>',print_r($ranking, true),'</pre>'; /**** OUTPUT ********** Array ( [Jemma] => 1 [Sylvia] => 2 [Eliza] => 2 [Joe] => 4 [Anne] => 5 [Marley] => 6 [Teddy] => 6 ) ***********************/ Quote Link to comment Share on other sites More sharing options...
vinny42 Posted November 2, 2013 Share Posted November 2, 2013 If there is a tie at number 2, the 3rd position is skipped Does this data come from a database perhaps? Because databased have functions to give you the ranking, no need for messing about with PHP. Quote Link to comment Share on other sites More sharing options...
bululu Posted November 2, 2013 Author Share Posted November 2, 2013 (edited) Does this data come from a database perhaps? Because databased have functions to give you the ranking, no need for messing about with PHP. If a tie occurs at the end, the preceding position is skipped, so if there are 3 students at number 9, we skip positions 7 and 8, so we should have. AS can be seen, 9 students sat for the test and the last scorers are at number 9. Positions are maintained though some have been forfeited e.g number 2, because two decided to be at number 1. 70 1 70 1 69 3 57 4 46 5 45 6 40 9 40 9 40 9 Yes, it comes from a MySQL Database. If the ranks/ positions were straightforward i.e just 1,2,3, etc then I would use mysql functions. I cannot see how to do it from MyQL as the data is being retrieved. Can MySQL functions handle this degree of ranking? Edited November 2, 2013 by bululu Quote Link to comment Share on other sites More sharing options...
Barand Posted November 2, 2013 Share Posted November 2, 2013 (edited) Here's how to do it in a query mysql> SELECT name, -> @row := @row+1 as row, -> @rank := IF(score=@prev, @rank, @row) as rank, -> @prev := score as score -> FROM score -> JOIN (SELECT @row:=0, @rank:=0, @prev:=0) as init -> ORDER BY score DESC; +-------+------+------+-------+ | name | row | rank | score | +-------+------+------+-------+ | David | 1 | 1 | 106 | | Sam | 2 | 2 | 76 | | Bob | 3 | 3 | 75 | | Jane | 4 | 3 | 75 | | Joe | 5 | 5 | 61 | | Mike | 6 | 5 | 61 | | Mary | 7 | 7 | 59 | +-------+------+------+-------+ Edited November 2, 2013 by Barand Quote Link to comment Share on other sites More sharing options...
Solution bululu Posted November 2, 2013 Author Solution Share Posted November 2, 2013 Here's how to do it in a query mysql> SELECT name, score, rank -> FROM -> ( -> SELECT name, -> @row := @row+1 as row, -> @rank := IF(score=@prev, @rank, @row) as rank, -> @prev := score as score -> FROM score -> JOIN (SELECT @row:=0, @rank:=0, @prev:=0) as init -> ORDER BY score DESC -> ) as rankings; +-------+-------+------+ | name | score | rank | +-------+-------+------+ | David | 106 | 1 | | Sam | 76 | 2 | | Bob | 75 | 3 | | Jane | 75 | 3 | | Joe | 61 | 5 | | Mike | 61 | 5 | | Mary | 59 | 7 | +-------+-------+------+ This looks great. I do not have the rank column, only the name and score. Users provide a name and a score and when that data is queried, the ranks are to be calculated. Do I need to add a rank column to the database? How does it work since I just supply the data for name and score? Quote Link to comment Share on other sites More sharing options...
kicken Posted November 2, 2013 Share Posted November 2, 2013 That query calculates the rank and row columns dynamically via variables. There is no need for either of those columns to be physically present in your tables. Quote Link to comment Share on other sites More sharing options...
Barand Posted November 2, 2013 Share Posted November 2, 2013 As Kicken said, the row and rank are calculated. My score table looks like this: +-------+-------+ | name | score | +-------+-------+ | Bob | 75 | | David | 106 | | Jane | 75 | | Joe | 61 | | Mary | 59 | | Mike | 61 | | Sam | 76 | +-------+-------+ Quote Link to comment Share on other sites More sharing options...
vinny42 Posted November 3, 2013 Share Posted November 3, 2013 Or you could just select the number of better scores per score: SELECT scores.*, (SELECT COUNT(*)+1 FROM scores AS betterscores WHERE betterscores.score < scores.score) AS rank FROM scores ORDER BY rank; or if your database supports windowing, just ask it for the ranking: SELECT id, score, RANK() OVER (order by score ASC) FROM scores Quote Link to comment Share on other sites More sharing options...
Barand Posted November 3, 2013 Share Posted November 3, 2013 Or you could just select the number of better scores per score: SELECT scores.*, (SELECT COUNT(*)+1 FROM scores AS betterscores WHERE betterscores.score < scores.score) AS rank FROM scores ORDER BY rank; You could if you want a highly inefficient solution. That solution uses a dependent subquery where every row has to run another query on the table. Quote Link to comment Share on other sites More sharing options...
vinny42 Posted November 3, 2013 Share Posted November 3, 2013 (edited) You could if you want a highly inefficient solution. That solution uses a dependent subquery where every row has to run another query on the table. True, if you have a few hundredthousand rows to examine you'd probably want to do it differently, using a left join and a count and a group-by; SELECT scores.id, COUNT(betterscores.score)+1 FROM scores LEFT JOIN scores AS betterscores ON betterscores.score < scores.score GROUP BY scores.id ORDER BY 2 ASC Edited November 3, 2013 by vinny42 Quote Link to comment Share on other sites More sharing options...
bululu Posted November 3, 2013 Author Share Posted November 3, 2013 Thanks guys - Barand, Vinny42 and Kicken, for sharing your expertise, I am learning alot and I really appreciate. This has been solved. Quote Link to comment Share on other sites More sharing options...
Barand Posted November 3, 2013 Share Posted November 3, 2013 True, if you have a few hundredthousand rows to examine you'd probably want to do it differently, using a left join and a count and a group-by; SELECT scores.id, COUNT(betterscores.score)+1 FROM scores LEFT JOIN scores AS betterscores ON betterscores.score < scores.score GROUP BY scores.id ORDER BY 2 ASC On a table with 180,000+ rows both the dependent subquery and left join versions timed out. The version with the SQL variables took < 0.9 seconds. Quote Link to comment Share on other sites More sharing options...
vinny42 Posted November 3, 2013 Share Posted November 3, 2013 On a table with 180,000+ rows both the dependent subquery and left join versions timed out. The version with the SQL variables took < 0.9 seconds. What was the test setup? Quote Link to comment Share on other sites More sharing options...
vinny42 Posted November 3, 2013 Share Posted November 3, 2013 Hmm, it seems that the join solution does indeed not work too well here. Looking at the explain paths it's logical because the join has to basically create a cartesian productand flatten that out to get the counts. Of course I'm going to annoy you by saying that PostgreSQL's windowing functions did the trick on 300.000 results in 0.4 seconds on an old 2GB dualcore laptop. :-) Quote Link to comment Share on other sites More sharing options...
Barand Posted November 3, 2013 Share Posted November 3, 2013 My 180,000 record table has no index on the "score" column so that could slow it Quote Link to comment Share on other sites More sharing options...
vinny42 Posted November 3, 2013 Share Posted November 3, 2013 Did you have any indexes when you tried the JOIN solutions? Because that would make your previous popst irrelevant. Quote Link to comment Share on other sites More sharing options...
Barand Posted November 3, 2013 Share Posted November 3, 2013 Same table in all tests Quote Link to comment Share on other sites More sharing options...
vinny42 Posted November 3, 2013 Share Posted November 3, 2013 Right... well, what can I say, except. Goodbye. I'm done here, I'll leave you guys along in your happy little bubbles... I'm off to find intelligent life... Quote Link to comment Share on other sites More sharing options...
ignace Posted November 3, 2013 Share Posted November 3, 2013 (edited) Ok. Bye. Edited November 3, 2013 by ignace Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.