sql - Mysql select top N scores per group FOR EACH User -
i'm trying buddy out "simple" website golf group. database i'm trying use store players scores every round completed. there 2 types of games played , stored. example below:
id game type score 1 12 1 12 1 1 1 15 1 15 1 b 12 1 b 5 1 b 10 1 b 12 1 b 5 1 b 10 2 6 2 9 2 1 2 3 2 2 2 b 8 2 b 10 2 b 15 2 b 3 2 b 12 what trying is, select top 3 scores of game type each user , select top 2 scores game type b each user. need select yield can sum scores each player:
id game type score 1 12 1 15 1 15 1 b 12 1 b 12 2 6 2 9 2 3 2 b 15 2 b 12 i found similar question , solution boncodigo @ select highest 3 scores in each day every user. altered sql this:
-- top 2 sum user each day select userid, sum(score), type scores t1 3 >= (select count(*) scores t2 t1.score <= t2.score , t1.userid = t2.userid , t1.type = t2.type order t2.score desc) group userid, type ; -- 2 days top 2 sum user select userid, sum(score) scores t1 3 >= (select count(*) scores t2 t1.score <= t2.score , t1.userid = t2.userid , t1.type = t2.type order t2.score desc) group userid ; the problem having if want top 3 scores, 3rd , 4th scores equal, not return 3rd highest score; top two. attached sqlfiddle i've been trying use. on how getting use top 3, no matter if equal or not! thanks!
so, being unable give on this, i've come monstrosity, using stored procedure desired output (essentially replicating you'd @ application level achieve same result).
create procedure leaderboard() begin declare uid int; declare done int default 0; declare cur1 cursor select distinct userid score; declare continue handler not found set done = 1; drop table if exists score_temp; create temporary table score_temp(userid int, score int, type int); open cur1; read_loop: loop fetch cur1 uid; if done close cur1; leave read_loop; end if; innerblock: begin declare done2 int default 0; declare i_userid int; declare i_type int; declare i_score int; declare cur2 cursor select * score userid = uid , type = 1 order score desc limit 3; declare continue handler not found set done2 = 1; open cur2; inner_loop: loop fetch cur2 i_userid, i_score, i_type; if done2 close cur2; leave inner_loop; end if; insert score_temp values (i_userid, i_score, i_type); end loop inner_loop; end innerblock; innerblock: begin declare done2 int default 0; declare i_userid int; declare i_type int; declare i_score int; declare cur2 cursor select * score userid = uid , type = 2 order score desc limit 2; declare continue handler not found set done2 = 1; open cur2; inner_loop: loop fetch cur2 i_userid, i_score, i_type; if done2 close cur2; leave inner_loop; end if; insert score_temp values (i_userid, i_score, i_type); end loop inner_loop; end innerblock; end loop read_loop; select * score_temp; end then leaderboard, query call leaderboard();
demo fiddle here: http://sqlfiddle.com/#!2/569fb2/1