Rank() over Partition by in mysql
25,587
Solution 1
Try this query: - MySql does not support Rank() function.
select result.id,result.login,result.rank from (
SELECT id,
login,
IF(login=@last,@curRank:=@curRank,@curRank:=@_sequence) AS rank,
@_sequence:=@_sequence+1,
@last:=login
FROM ds , (SELECT @curRank := 1, @_sequence:=1, @last:=0) r
ORDER BY id asc) as result;
Hope it helps you!
Solution 2
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>...}]
)
Solution 3
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
Author by
Samuel Ellett
Updated on April 27, 2020Comments
-
Samuel Ellett about 4 years
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!
-
Samuel Ellett almost 7 yearsThankyou. I leveraged your response above and it worked well.
-
Barmar over 6 yearsShouldn't it be "does not support"?