How to increment rank when same total marks is there using mysql query?

Question

This is my query:

SELECT
    `users`.`id` AS userID,
    `user_tests`.`id`,
    `users`.`profilePic`,
    `users`.`firstName`,
    `user_tests`.`userId`,
    `user_tests`.`isFirstAttempt`,
    `user_tests`.`total_marks`,
    FIND_IN_SET(
        `user_tests`.`total_marks`,
        (
        SELECT
            GROUP_CONCAT(
                DISTINCT `user_tests`.`total_marks`
            ORDER BY
                CAST(
                    `user_tests`.`total_marks` AS DECIMAL(5, 3)
                )
            DESC
            )
        FROM
            `user_tests`
        WHERE
            `user_tests`.`testSeriesId` = '856' AND `user_tests`.`isFirstAttempt` = '1'
    )
    ) AS rank,
FROM
    `user_tests`
LEFT JOIN `users` ON `users`.id = `user_tests`.`userId`
WHERE
    `user_tests`.`isFirstAttempt` = '1' AND `user_tests`.`testSeriesId` = '856'
ORDER BY
    CAST(
        `user_tests`.`total_marks` AS DECIMAL(5, 3)
    )
DESC
    ,
    `submissionTimeInMinutes` ASC,
    `rank` ASC;

Output:

this is the image

Expected:

Here is the expected output image

I am using MariaDB 5.5.68 I've tried using variables to increment but it's showing me the current row number instead of 1,2,3,4,5... numbers.

Can anybody help here? Thanks.

Does this answer your question? [ROW\_NUMBER() in MySQL](https://stackoverflow.com/questions/1895110/row-number-in-mysql) — Markus Zeller, Jun 26 '22 at 17:30
No, as I said I am using MariaDB 5.5.68, so it does not support rank() or row_number(). — Sandeep Acharya, Jun 26 '22 at 17:32
ROW_NUMBER is also supported by MariaDB 10.2+. So you will have bad luck. https://mariadb.com/docs/reference/mdb/functions/ROW_NUMBER/ — Markus Zeller, Jun 26 '22 at 17:36

shawnt00 · Answer 1 · 2022-06-27T04:55:52.127

You don't appear to need a left join as none of the user names are missing. A simple subquery will give you the the ranking by total_mark but I'm not sure whether you're counting users or something else. A CTE would let you avoid duplicating some of the logic and might possibly even be faster. I don't know if you have that option.

SELECT
    u.id AS userID, t.id, u.profilePic, u.firstName,
    t.userId, t.isFirstAttempt, t.total_marks,
    (
        SELECT count(distinct userId) from FROM user_tests t2
        WHERE     t.isFirstAttempt = 1 AND t.testSeriesId = 856
              AND t.total_marks < t2.total_marks
    ) as num_tests
FROM user_tests t inner join users u ON u.id = t.userId
WHERE t.isFirstAttempt = 1 AND t.testSeriesId = 856
ORDER BY total_marks desc, submissionTimeInMinutes, num_tests desc

This query keeps loading and does not generate any output. Not sure, something is wrong with this query — Sandeep Acharya, Jun 27 '22 at 03:53
Try again without the string conversions and see what that does. — shawnt00, Jun 27 '22 at 04:56

How to increment rank when same total marks is there using mysql query?

1 Answers1