CODEWITHSUNDEEP
phpsqlmariadb
Lavesh Karnani
Lavesh Karnani

Reputation: 43

Ranking based on two columns

Finding the Rank of the Particular User from the Database by comparing three columns.

Name | Score | Attempts | Time
test |   2   |   4      |  2019-01-29 19:50:11
tes2 |   2   |   1      |  2019-01-29 20:14:11

So the Expected Output should be 

Name | Score | Attempts | Time                  | Rank
tes2 |   2   |   1      |  2019-01-29 20:14:11  |  1
test |   2   |   4      |  2019-01-29 19:50:11  |  2

If I wanted to search Rank for User 'test' then I should get Rank 2 as my Answer.

I have done the part from which I can get the overall Rank, but I couldn't able to find Rank for Individual User.

This is code I have written for getting Overall Rank.

select t.*, @r := @r + 1 as `new_rank`
from  tbl t,
(select @r := 0) r
order by `Rank` asc, `Tasks` desc`

SQL Version that I'm Using is 10.1.9-MariaDB

Upvotes: 2

Views: 11002

Answers (2)

AkhilKrishnan
AkhilKrishnan

Reputation: 534

You can use dense_rank, helps you to rank in numerical order.

*Syntax : 
    DENSE_RANK() OVER (
       [ PARTITION BY partition_expression ]
       [ ORDER BY order_list ]
    )* 

select t.*, dense_rank() over (order by rank asc, task desc) as new_rank
from t;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269743

Use row_number() or rank(), depending on how you want ties handled. For instance:

select t.*, row_number() over (order by rank asc, task desc) as new_rank
from t;

If you are using older versions of MySQL (as your code snippet suggests), then you can use variables, but you probably need a subquery:

select t.*, (@r := @r + 1) as new_rank
from (select t.* from tbl t order by Rank asc, Tasks desc) t cross join
     (select @r := 0) r;

One method to get a particular row:

select t.*
from (select t.*, (@r := @r + 1) as new_rank
      from (select t.* from tbl t order by Rank asc, Tasks desc) t cross join
           (select @r := 0) r
     ) t
where t.name = ?;

Upvotes: 1

Related Questions