CODEWITHSUNDEEP
mysql
Fahim Parkar
Fahim Parkar

Reputation: 31647

Ranking joint positions in MySQL

Below is what I have

  userid score 
    1       8    
    2       5    
    3       4    
    4       4    
    5      10    
    6       3

What I want is as below

userid score position
    5      10     1
    1       8     2
    2       5     3
    3       4     4
    4       4     4
    6       3     5

NOTE:

I have code where I have created below output, 

userid score position
    5      10     1
    1       8     2
    2       5     3
    3       4     4
    4       4     4
    6       3     6

Code is 

SELECT userid, score, 
(SELECT COUNT(*) FROM fschema.mytab3 u2 
WHERE 
u2.score > u1.score) + 1 AS position FROM fschema.mytab3 u1
ORDER BY position

I want user 6 to have position as 5 instead of 6

Upvotes: 0

Views: 444

Answers (3)

fortran
fortran

Reputation: 76077

What about this?

SELECT userid, score, 
(SELECT COUNT(distinct u2.score) FROM fschema.mytab3 u2 
WHERE 
u2.score > u1.score) + 1 AS position FROM fschema.mytab3 u1
ORDER BY position

Upvotes: 2

Devart
Devart

Reputation: 122002

Try this one -

SELECT
  *,
  @r:=IF(@score IS NULL OR @score <> score, @r+1, @r) position, @score:=score
FROM
  fschema.mytab3,
  (SELECT @r:=0, @score:=NULL) t
ORDER BY
  score DESC, userid

Upvotes: 1

Tomas Narros
Tomas Narros

Reputation: 13488

I think that you could try it using variables, which looks easier. Something like this:

SELECT userid, score, @rownum:=@rownum+1 as position
FROM fschema.mytab3 u1, (SELECT @rownum:=0) r
ORDER BY score;

(Currently I'm unable to check MySql queries, please, excuse me if there is some error)

Upvotes: 0

Related Questions