Jump to content
gmc1103

ranking based on score and time elapsed

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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 by Barand

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.