1

Need to list a Hall of Fame of best players, the database contains each single game player in different games.

The level has the priority, if the level are the same, check the highest score.

I've a database with user_id, level, score, game and data. Schema here:

CREATE TABLE IF NOT EXISTS `docs` (`user_id` int(6) unsigned NOT NULL,
`level` int(3) unsigned NOT NULL,`game` varchar(30) NOT NULL,
`score` int(5) unsigned NOT NULL,
`data` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `docs` (`user_id`, `level`, `game`, `score`,`data`) VALUES
  ('1', '7', 'pacman', '8452','2018-02-14 15:00:00'),
  ('1', '9', 'pacman', '9999','2018-02-10 16:30:00'),
  ('2', '8', 'pacman', '8500','2018-02-24 17:30:00'),
  ('1', '10', 'pacman', '9100','2018-02-15 18:30:00'),
  ('1', '10', 'pacman', '8800','2018-02-15 18:11:00'),
  ('1', '11', 'snake', '9600','2018-02-14 15:00:00'),
  ('1', '6', 'snake', '7020','2018-02-11 11:30:00'),
  ('2', '8', 'snake', '8500','2018-02-24 14:00:00'),
  ('2', '12', 'snake', '9200','2018-02-25 19:00:00'),
  ('2', '12', 'snake', '9800','2018-02-25 19:20:00'),
  ('1', '4', 'pacman', '2452','2018-03-11 15:00:00'),
  ('1', '6', 'pacman', '4999','2018-03-07 16:30:00'),
  ('2', '7', 'pacman', '5500','2018-03-02 17:30:00'),
  ('1', '7', 'pacman', '5100','2018-03-01 18:30:00'),
  ('1', '3', 'snake', '3600','2018-03-03 15:00:00'),
  ('1', '5', 'snake', '4220','2018-03-01 11:30:00'),
  ('2', '5', 'snake', '3900','2018-03-04 14:00:00'),
  ('2', '5', 'snake', '5200','2018-03-05 19:00:00');

i want retrieve the hall of fame for selected month and game, for example if i choose pacman on march the result should be:

user level score
2     7    5500
1     7    5100

i tryed this how suggest in other similar topic

select d1.*
from docs d1 
left outer join docs d2
on (d1.user_id = d2.user_id and d1.level < d2.level)
where d2.user_id is null
order by level desc;

but i've duplicate levels for same user, then i cant choose the game or the month.

here there is the SQL Fiddle

Lelio Faieta
  • 6,457
  • 7
  • 40
  • 74
JoTaRo
  • 173
  • 12

2 Answers2

2
SELECT x.* FROM docs x
JOIN 
(select user_id
      , game
      , MONTH(data) month
      , MAX(score) score
   from docs 
  where game = 'pacman' 
    and MONTH(data) = 3 
  group 
     by user_id
      , game
      , MONTH(data)
 ) y
 ON y.user_id = x.user_id
 AND y.game = x.game
 AND y.month = MONTH(x.data)
 AND y.score = x.score;

or something like that

Strawberry
  • 33,750
  • 13
  • 40
  • 57
1

after a long work, study and research this is the best solution for me:

SELECT user_id, level, score, game
  FROM (
            SELECT *, 
                   @rn := IF(user_id = @g, @rn + 1, 1) rn, 
                   @g := user_id
              FROM (select @g := null, @rn := 0) x, 
                   docs where game='pacman'
          ORDER BY user_id, level desc, score desc, game
       ) X
 WHERE rn = 1 order by level desc, score desc;

the explanation is in this topic Select one value from a group based on order from other columns

JoTaRo
  • 173
  • 12