MySQL Ranking ID with Ties Based on Column Returns Error

Question

I have a 1 table database with (in a simplified form) the following fields: user_id(INT), ref_id(INT), points(INT), pointsgiven(BOOL/TINY_INT)

I want a query that returns the RANK of the user_id I specify based on points, given that pointsgiven is true. The kicker is, I need ties included. I can get a result set for ALL ranks if I want with the following query

SELECT 
user_id, ref_id, points, pointsgiven, 
CASE 
        WHEN @points = COALESCE(points, 0) THEN @rownum 
        ELSE @rownum := @rownum + 1 
END AS rank,
@points := COALESCE(points, 0)
FROM users CT
JOIN 
(
        SELECT @rownum := 0, @points := NULL
) r
WHERE pointsgiven=TRUE ORDER BY points DESC

So, based on that, I thought I could just use that as a subquery to get a certain user_id as follows:

select * from 
(
    SELECT 
    user_id, ref_id, points, pointsgiven, 
    CASE 
            WHEN @points = COALESCE(points, 0) THEN @rownum 
            ELSE @rownum := @rownum + 1 
    END AS rank,
    @points := COALESCE(points, 0)
    FROM users CT
    JOIN 
    (
            SELECT @rownum := 0, @points := NULL
    ) r
    WHERE pointsgiven=TRUE ORDER BY points DESC
) as derived WHERE user_id = 15

But this returns [BLOB - 1 B] as the rank on the correct user_id. What am I doing wrong here?

You may want to check out this fantastic answer regarding detecting ties in rank order: http://stackoverflow.com/questions/8767323/how-best-to-get-someones-rank-from-a-scores-table-with-php-and-mysql-without — hannebaumsaway, Jul 30 '13 at 00:30
.. Yes Derick, that's where I'm testing it. Why do you say that? Praguian, thanks for the link! — AMB0027, Jul 30 '13 at 04:19

score 1 · Accepted Answer · answered Jul 30 '13 at 00:26

1

I have no idea why your query isn't working. For a single user id, though, you can use a correlated subquery:

select user_id, ref_id, points, pointsgiven,
       coalesce((select count(distinct user_id)
                 from users u2
                 where u2.pointsgiven=TRUE and
                       u2.points > u.points
                ) + 1, 1) as rank
from users u
where user_id = 15;

An index on users(pointsgiven, points, user_id) should be used by the query.

To look at just one ranking, this might even be faster than your method.

answered Jul 30 '13 at 00:26

Gordon Linoff

1,242,037
58
646
786

Gordon. Thanks for your answer it appears to do exactly as I asked. However, though it doesn't rank users with pointsgiven=FALSE, it still returns them in the set. I didn't specify whether they should or shouldn't be returned, but it is a simple fix none the less. Thanks again, and if you see any problem with my edit with solved statement, please just let me know! – AMB0027 Jul 30 '13 at 04:26

MySQL Ranking ID with Ties Based on Column Returns Error

1 Answers1