Get position rank of specific row using MySQL query

Question

I have 2 tables:

• user
• checked

Each user has to read a book to finish a course. The system keeps track of each line read by the users in the table checked. The users are ranked depending on how many lines they've read; the more you read, the better ranked you are.

For example, there are 5 users:

• Andres, 8 lines read (Andres' position should be 1)
• Romina, 7 lines read (Romina's position should be 2)
• John, 5 lines read (John's position should be 3)
• Mona, 5 lines read (Mona's position should be 3)
• Joey, 0 lines read (Joey's position should be 5)

Here's a sqlfiddle with the structure and data. I'm using John for this example:

Get all the lines read by John:

SELECT COUNT(*) AS q FROM checked WHERE id_user = 3;

Get a list of all the users in the ranked order:

SELECT
user.id_user AS id_user,
user.name AS name,
COUNT(checked.id_check) AS q
FROM user
JOIN checked ON checked.id_user = user.id_user
GROUP BY checked.id_user ORDER BY q;

The issue is that I still need a way to get the position rank of a specific user, and only that. For the John example, the query should return 3.

If we're still using John as the example, my initial idea would be to get John's read line count and then count all the users who have a read line count greater than John's, and add 1.

How could I put that into a MySQL (5.6) query? Of courses other alternatives are welcome!

Any ideas? Thanks!

Answer 1

You are looking for the RANK() analytic function:

SELECT
    u.id_user AS id_user,
    u.name AS name,
    COUNT(c.id_check) AS q,
    RANK() OVER (ORDER BY COUNT(c.id_check) DESC) position
FROM user u
LEFT JOIN checked c
    ON c.id_user = u.id_user
GROUP BY
    u.id_user,
    u.name
ORDER BY
    q;

Note that RANK requires MySQL version 8 or later. If you are using an earlier version of MySQL, there are ways to simulate rank, but they are fairly ugly, and if you expect to have a long term need, then consider upgrading.

Edit:

One possible workaround for MySQL versions 5.7 and earlier would be to use a correlated subquery to find the rank:

SELECT
    id_user,
    name,
    q,
    1 + (SELECT COUNT(*) FROM
        (SELECT COUNT(c.id_check) cnt FROM user u
         LEFT JOIN checked c ON c.id_user = u.id_user
         GROUP BY c.id_user) t
     WHERE q < t.cnt) AS `rank`
FROM
(
    SELECT
        u.id_user AS id_user,
        u.name AS name,
        COUNT(c.id_check) AS q
    FROM user u
    LEFT JOIN checked c
        ON c.id_user = u.id_user
    GROUP BY
        u.id_user,
        u.name
) t;

Here is a demo for the MySQL 5.7 workaround query:

Demo