-4

I have the below SQL query from Stack Overflow, and it's working perfectly. This is the link to the original answer.

I want the below query to output the rank in the following order if there is any tie:

1,1,3,4,5,6,6,6,9,10.... etc.

This is the SQL query:

SELECT idno, name, rank,total_score
FROM (SELECT *,  IF(@marks=(@marks:=total_score), @auto, @auto:=@auto+1) AS    rank 
FROM (SELECT * FROM 
 (SELECT idno, name, SUM(score) AS total_score 
FROM jbit, 
(SELECT @auto:=0, @marks:=0) as init 
 GROUP BY name) sub ORDER BY total_score DESC)t) as result
WHERE idno ='1'
Community
  • 1
  • 1
Amacyber
  • 1
  • 1
  • The original answer claims this code already handles ties. What is the problem you are experiencing, exactly? – halfer Apr 07 '15 at 08:32
  • Uh-oh. Have you asked five questions on the same topic? – halfer Apr 07 '15 at 08:34
  • 1
    Readers answering this question may first wish to check the OP's ranking SQL questions: [one](http://stackoverflow.com/q/29388723), [two](http://stackoverflow.com/q/29391651), [three](http://stackoverflow.com/q/29451275), [four](http://stackoverflow.com/q/29452337) and [five](http://stackoverflow.com/q/29486804), so that work is not accidentally duplicated. – halfer Apr 07 '15 at 08:48
  • Thank you @halfer, my problem now is this; if two users have the same SUM(score), and there ranking will be, first user 1 and second user 1, i want the score of the third user to be 3 instead of 2, this is order of which i want the query to represent the ranking. please help me out. – Amacyber Apr 07 '15 at 09:18

1 Answers1

0

MySQL can sort by values even with ties using the standard ORDER BY. It'll return result rows in order as you mentioned; 1, 1, 3, 4, 5, 6, 6, 6, 9, 10.

SELECT idno, name, rank, total_score FROM table_name ORDER BY rank;
DACrosby
  • 11,116
  • 3
  • 39
  • 51
  • thank your @DACrosby, but i am not really seeking for the query to sort by that order, but rather i want the query to return, the ranking of users in that order, lets assume USER1 ranks 2, and USER2 ranks 2, i want USER3 to be ranked 3rd instead of 2nd. thank you – Amacyber Apr 07 '15 at 09:21
  • Okay, so instead of `1, 1, 3, 4...` you'd like `1, 2, 3, 4...`. Try `GROUP BY rank ORDER BY rank` - the grouping should cut out duplicate ranks. – DACrosby Apr 07 '15 at 09:49
  • thank you @DACrosby, but it is giving me error, i want the rank position to be in this order 1,2,2,4,5,6,6,8 etc. thank you – Amacyber Apr 09 '15 at 15:57