MySQL ranking based on score and time played
<p>I have two MySQL tables as follows</p>

<p><strong>Table: <em>score</em></strong></p>

<p><a href="https://i.stack.imgur.com/Y24zn.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Y24zn.png" alt="enter image description here"></a></p>

<p><strong>Table: <em>user</em></strong></p>

<p><a href="https://i.stack.imgur.com/kkcfT.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/kkcfT.png" alt="enter image description here"></a></p>

<p>My requirement is to rank the score and retrieve if the score total_score is greater than some value (for example rank all score if total_score is less than 15). If scores are equal then consider time too.</p>

<p>I wrote the following query to fetch the rows including rank (I'm not much familiar with SQL queries but I tried to findout a way to do this - no luck).</p>

<p><strong>Attempt - 1</strong></p>

<pre><code>SELECT score.total_score,
       CONCAT(users.user_first_name,' ', users.user_last_name) AS name,
       users.user_email,
       FIND_IN_SET(total_score,
                     (SELECT GROUP_CONCAT(total_score
                                          ORDER BY total_score ASC)
                      FROM score)) AS rank
FROM score
INNER JOIN users ON score.user_id = users.user_id
WHERE score.total_score &gt; 15
ORDER BY score.total_score DESC LIMIT 10
</code></pre>

<p><strong>Attempt - 2</strong></p>

<pre><code>SET @Rank := 0;
SELECT score.total_score,score.time,
       CONCAT(users.user_first_name,' ', users.user_last_name) AS name,
       users.user_email,
       @Rank := @Rank + 1 AS rank
FROM score
INNER JOIN users ON score.score_id = users.user_id
WHERE score.total_score &gt; 15
ORDER BY score.total_score DESC, time ASC LIMIT 10
</code></pre>

<p>Both attempts are failed, in short what I tried to do is,</p>

<p>Rank user scores according to total_score by considering time (there is chances to have same scores).</p>

<p>Thanks in advance.</p>