2

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!

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Samuel Ellett
  • 37
  • 1
  • 1
  • 4

3 Answers3

9

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!

Rohit Padma
  • 603
  • 5
  • 15
4

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

Tolga Sahin
  • 319
  • 2
  • 6
1

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
Thiago
  • 101
  • 1
  • 6