CODEWITHSUNDEEP
phpmysqlsql
Balasaheb Shinde
Balasaheb Shinde

Reputation: 3

How can I calculate rank based on highest Score and lowest Minutes in MySql Server

My Table Structure is 

CREATE TABLE IF NOT EXISTS `tbl_user_result` (
   `Id` int(11) NOT NULL AUTO_INCREMENT,
   `ResultId`int(11),
   `PaperId` int(11) ,
   `Title` varchar(45),
   `TotalQuestions` int(11) ,
   `Attempt` int(11) ,
   `Correct` int(11) ,
   `Wrong` int(11) ,
   `Score` int(45) ,
   `Minutes` int(11) ,
   `TimeSt` varchar(45) ,
   `UserEmail` varchar(45) ,
   `UserName` varchar(45) ,
  PRIMARY KEY (`Id`));

I tried following SQL query

SELECT 
    id,
    PaperId,
    Title,
    Score,
    Minutes,
    (SELECT 
            COUNT(*) + 1
        FROM
            tbl_user_result 
        WHERE
            Score > x.Score AND Minutes > x.Minutes) AS Rank
FROM
    `tbl_user_result` as x

Upvotes: 0

Views: 469

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

The easiest way is to use variables:

select r.*
from (select r.*, (@rn := @rn + 1) as rank
      from tbl_user_result r cross join (select @rn := 0) vars
      order by score desc, minutes asc
     ) r;

This works if you have no duplicates.

I think the following is the version of your query that you want:

SELECT id, PaperId, Title, Score, Minutes,
       (SELECT COUNT(*) + 1
        FROM tbl_user_result r2
        WHERE r2.Score > r.Score OR
              (r2.Score = r.Score AND r2.Minutes < r.Minutes)
       ) AS Rank
FROM  tbl_user_result r;

Upvotes: 4

Related Questions