How to Use Find in Set for User Rankings Based on Score

How to use find in set for user rankings based on score?

If You don't insist on using find_in_set, you can get result with simple join. You ask for list of users (p) and for each user you ask, how many users have better score than him or her (c):

SELECT p.userid, COUNT(c.userid) AS rank
FROM users AS p
LEFT JOIN users AS c ON c.score > p.score
GROUP BY p.userid

This works even if you add other conditions, like WHERE p.userid = 123.
If more users have the same score, the ranks would look like 0,1,2,2,2,5,6.

Number of users in a ranking MySQL query

Check this ... I hope it help you ...

SELECT user_id,@all_user := (SELECT COUNT(*) FROM `results`) as all_count,((@all_user-score)+1) as rank FROM `results` WHERE user_id = 3 

MySQL, Get users rank

SELECT  uo.*, 
        (
        SELECT  COUNT(*)
        FROM    users ui
        WHERE   (ui.points, ui.id) >= (uo.points, uo.id)
        ) AS rank
FROM    users uo
WHERE   id = @id

Dense rank:

SELECT  uo.*, 
        (
        SELECT  COUNT(DISTINCT ui.points)
        FROM    users ui
        WHERE   ui.points >= uo.points
        ) AS rank
FROM    users uo
WHERE   id = @id

Find the best match in MySQL for user-ranked list

I can't quite get this to work using SQL only but a simple PHP solution would be similar to the following (untested):

//Array of $productId => list of feature ids
$products;
$userFeatures;
$scores = array();
//For every product work out a score based on features and user ranking.
foreach($products as $productId => $prodFeatures){
    $score = 0;
    foreach($prodFeatures as $feature){
        //Could also perhaps penalise products lacking features.
        $score += $userFeatures[$feature]["rank"];
    }
    $scores[$productId] = $score;
}
arsort($scores);
echo "Best match is ".$scores[0];

Obviously this is a bit rough and ready but hopefully it helps.

Edit: This assumes that a ranking of 10 is the best.

How to get ranking from a scoreboard of 5million rows in MySQL and PHP?

The best way to do this is to use a table that is updated on a regular basis (once an hour, once a day etc) by a schedule that can take a longer query and run it. There isn't any need to smash the database all the time with those sorts of heavy queries. It also means that the long-running query is run once a while, not with every user that browses through o that page.

A perfect example is here on SO, where the weekly/monthly/yearly totals are updated once per day.

---

Related Topics