3

I have a query on MySQL database working in a such way that I get this result:

> Rank  Gold    Silver  Bronze  Total 
> 1     76      78      77      231 
> 2     4       5       6       15 
> 3     4       1       1       6
> 4     3       0       0       3 
> 5     2       1       1       4 
> 5     2       1       1       4
> 6     2       0       0       2
> 7     1       1       1       3
> 8     1       1       0       2
> 9     1       0       2       3
> 9     1       0       2       3
> 9     1       0       2       3
> 10    0       1       0       1

The query from which I get this result is the following (I hide the main query not to wright too much code):

select
    CASE
        WHEN (@Gold=T.Gold and @Silver=T.Silver and @Bronze=T.Bronze) THEN @rownum ELSE @rownum:=@rownum+1 end as Rank,        

        (@Gold:=T.Gold) Gold,
        (@Silver:=T.Silver) Silver,
        (@Bronze:=T.Bronze) Bronze,
        T.Total

from

(MAIN_QUERY) T,

(SELECT @rownum:=0) r, 
    (SELECT @Gold:=0) g,
    (SELECT @Silver:=0) s,
    (SELECT @Bronze:=0) b

   order by Gold desc, Silver DESC, Bronze DESC

But instead the reuslt above, I'd like to adjust the Rank coloumn (and my query) increasing it depending on the duplicated previous rows, i.e.:

> Rank  Gold    Silver  Bronze  Total 
    > 1     76      78      77      231 
    > 2     4       5       6       15 
    > 3     4       1       1       6
    > 4     3       0       0       3 
    > 5     2       1       1       4 
    > 5     2       1       1       4
    > 7     2       0       0       2
    > 8     1       1       1       3
    > 9     1       1       0       2
    > 10    1       0       2       3
    > 10    1       0       2       3
    > 10    1       0       2       3
    > 13    0       1       0       1

and so on. Can you help me to get this result?

Drew
  • 24,851
  • 10
  • 43
  • 78
Idro
  • 253
  • 1
  • 7
  • What about rank 6 in the example above? And what's the expected criteria for this expected ranking – Hanky Panky Aug 04 '16 at 04:58
  • If it was me, I'd have a column for country, a column for colour, and a column for quantity, and take it from there. – Strawberry Aug 04 '16 at 05:02
  • @hankypanky that's the point. The op wants to handle ties in the same that a sports scoreboard would – Strawberry Aug 04 '16 at 05:04
  • But then does a real score board skip a whole rank? Its fine to have two teams on rank 5 but is it also fine to have 0 teams on rank 6 then resume from 7? – Hanky Panky Aug 04 '16 at 05:08
  • 1
    You could introduce another variable with a group counter. If no one answers, ping me. – Drew Aug 04 '16 at 05:11
  • maybe you could add a sqlfiddle to illustrate –  Aug 04 '16 at 05:11
  • @Drew: yes, I tried to use another variable for group counter but with no results. Maybe I was wrong something. How could you do? Can you help me? – Idro Aug 04 '16 at 08:11
  • Sure. You need to add a sqlfiddle with data and your your entire query. Please set all that up and I will take a look – Drew Aug 04 '16 at 13:10

1 Answers1

2

Maybe another variable can do this:

SELECT @rownum := @rownum + 1, 
       CASE 
         WHEN ( @gold = T.gold 
                AND @silver = T.silver 
                AND @bronze = T.bronze ) THEN @rank := @rank 
         ELSE @rank := @rownum 
       END                     AS Rank, 
       ( @gold := T.gold )     Gold, 
       ( @silver := T.silver ) Silver, 
       ( @bronze := T.bronze ) Bronze, 
       T.total 
FROM   (MAIN_QUERY) T 
       CROSS JOIN (SELECT @rownum := 0, 
                          @gold := 0, 
                          @silver := 0, 
                          @bronze := 0, 
                          @rank := 0) v 
ORDER  BY gold DESC, 
          silver DESC, 
          bronze DESC

Here is a demo

NOTE: If all columns of your data are 0, this will not work.

Blank
  • 12,308
  • 1
  • 14
  • 32
  • The MySQL manual makes it clear that the ordering of which select columns are computed are not guaranteed. So this is not a safe strategy. Please see [This](http://i.imgur.com/r6avUZT.jpg). So getting it to work in a fiddle is one thing. But I would never deploy it to production. – Drew Aug 04 '16 at 05:25
  • @Drew Hmm, right. What if `order by` in main query? – Blank Aug 04 '16 at 05:27
  • I created the tag above to house some as I restumble onto them. `mysql-variables` . Only a few of us on the stack write up the answers the safe way. There is the Baron's [Obligatory Reading](http://www.xaprb.com/blog/2006/12/15/advanced-mysql-user-variable-techniques/) blog where he finds safety in `greatest()`. Things can be cleansed with `least()`, `greatest()`, and `coalesce()` or layers of `if` stmts that seem to make no sense but there is method to the madness. – Drew Aug 04 '16 at 05:40
  • But they are time consuming. So one's biorhythms and mojo have to be into it at the moment. Plus the question has to be well setup. – Drew Aug 04 '16 at 05:41