Rank() over Partition by in mysql

user_2608677 0
Viewed 25962

I'm completely stumped as to create a new column "LoginRank" from rank() over(partition by x, order by y desc) in mysql.

From sql server i would write the following query, to create a column "Loginrank" that is grouped by "login" and ordered by "id".

select ds.id, 
       ds.login, 
       rank() over(partition by ds.login order by ds.id asc) as LoginRank
from tablename.ds

I have the following table.

create table ds (id int(11), login int(11))
insert into ds (id, login) 
values  (1,1),
    (2,1),
    (3,1),
    (4,2),
    (5,2),
    (6,6),
    (7,6),
    (8,1)

I tried applying many existing mysql fixes to my dataset but continue to have issues.

Any help is greatly appreciated. Thanks!

partition-by rank window-functions mysql
3 Answers

After Mysql 8.0 you can use Rank function

 RANK() OVER (
    ORDER BY column_name
) my_rank

RANK() OVER (
PARTITION BY <expression>[{,<expression>...}]
ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)

here is the usage

user_55736160

Try this:

SELECT a.id, a.login, count(b.id)+1 as loginRank
FROM ds a left join ds b ON a.id>b.id AND a.login=b.login
GROUP BY a.id, a.login
ORDER BY a.login, loginRank

You will get:

id | login | loginRank
 1 |   1   |    1
 2 |   1   |    2
 3 |   1   |    3
 8 |   1   |    4
 4 |   2   |    1
 5 |   2   |    2
 6 |   6   |    1
 7 |   6   |    2
user_17598990
Related