ranking based on score and time elapsed

[gmc1103]

Hi

 

I 'm trying to make a ranking based on score and time elapsed but i only get it working with score

 

this is my table with dummy data

id_ranking  id_jogo  id_user  pontuacao  data        tempo     
----------  -------  -------  ---------  ----------  ----------
         3     5141       11         15  2016-12-27  00:00:03  
         4     7001        9         10  2016-12-27  00:00:06  
         5     2519        7          5  2016-12-27  00:00:07  
         6     4585        6         15  2016-12-27  00:00:09 

So i need to make a ranking based on (pontuacao field, and tempo field), this what i have so far

SELECT t2.nome, t1.pontuacao, t1.data, t1.tempo, FIND_IN_SET( t1.pontuacao,(
SELECT GROUP_CONCAT( t1.pontuacao
ORDER BY t1.pontuacao DESC, t1.tempo ASC ) 
FROM ranking AS t1 )
) AS rank
FROM ranking AS t1
INNER JOIN users AS t2
ON (t1.id_user = t2.id_user) 

With this return 

nome        pontuacao  data        tempo       rank  
----------  ---------  ----------  --------  --------
testes             15  2016-12-27  00:00:03         1
dasffgfadg         10  2016-12-27  00:00:06         3
dasgsdfg            5  2016-12-27  00:00:07         4
gil                15  2016-12-27  00:00:09         1

But i have 2 (1) and since one used 3 seconds and the other 9 seconds, i want both of them differenciate

 

any help?

[Barand]

test data

insert into ranking (id_jogo, id_user, pontuacao, data, tempo) 
VALUES
(5141,       11 ,        15, '2016-12-27', '00:00:03'),  
(7001 ,       9 ,        10, '2016-12-27', '00:00:06'), 
(2519 ,       7 ,         5, '2016-12-27', '00:00:07'), 
(4585 ,       6 ,        15, '2016-12-27', '00:00:04'), 
(4585 ,       5 ,        10, '2016-12-27', '00:00:05'), 
(4585 ,       4 ,        10, '2016-12-27', '00:00:07'), 
(4585 ,       3 ,        6,  '2016-12-27', '00:00:07'), 
(4585 ,       2 ,        7,  '2016-12-27', '00:00:07'), 
(4585 ,       1 ,        10, '2016-12-27', '00:00:06'); 

query

SELECT 
  id_user
, pontuacao
, data
, tempo
, rank
FROM (
    SELECT
      id_user
    , data
    , @row := @row+1 as row
    , @rank := IF(@prevpont = pontuacao AND @prevtemp = tempo, @rank, @row) as rank
    , @prevpont := pontuacao as pontuacao
    , @prevtemp := tempo as tempo
    FROM ranking
        JOIN (SELECT @prevtemp:='0:00:00', @prevpont:=0, @row:=0, @rank:=0) init
    ORDER BY pontuacao DESC, tempo
    ) calc 
;

results

+---------+-----------+------------+----------+--------+
| id_user | pontuacao | data       | tempo    | rank   |
+---------+-----------+------------+----------+--------+
|      11 |        15 | 2016-12-27 | 00:00:03 |      1 |
|       6 |        15 | 2016-12-27 | 00:00:04 |      2 |
|       5 |        10 | 2016-12-27 | 00:00:05 |      3 |
|       9 |        10 | 2016-12-27 | 00:00:06 |      4 |  =
|       1 |        10 | 2016-12-27 | 00:00:06 |      4 |  =
|       4 |        10 | 2016-12-27 | 00:00:07 |      6 |
|       2 |         7 | 2016-12-27 | 00:00:07 |      7 |
|       3 |         6 | 2016-12-27 | 00:00:07 |      8 |
|       7 |         5 | 2016-12-27 | 00:00:07 |      9 |
+---------+-----------+------------+----------+--------+

Edited December 27, 2016 by Barand

[gmc1103]

Hi barand

Exactly what i need..

Thanks, and Happy new year