clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 756624 fiddles created (13585 in the last week).

drop table if exists users;
 hidden batch(es)


drop table if exists views;
 hidden batch(es)


drop table if exists tracks;
 hidden batch(es)


create table users (idu int ,username varchar(30));
 hidden batch(es)


create table views (id int ,`by` int ,track int);
 hidden batch(es)


create table tracks (id int ,uid int ,title varchar(30) ,time datetime);
 hidden batch(es)


insert into users values (567,'TrainingPuppy'), (687,'BathroomMakeover'), ( 45,'PoshNachos'), ( 15,'SewingButtons');
 hidden batch(es)


insert into views values (1, 5, 55), (2, 5, 55), (3, 67, 55), (4, 6, 29), (5,125, 2), (6, 5,698), (7, 5,698);
 hidden batch(es)


insert into tracks values ( 2, 15,'Worf is in the air','2016-02-11 22:57:35'), ( 29,567,'Stargold' ,'2016-08-11 22:57:28'), ( 55,567,'No love liers' ,'2016-10-11 22:57:51'), (698,567,'Lofe' ,'2016-11-11 22:57:44');
 hidden batch(es)


select id, uid, ucount as 'count', username, track, title, time from (select id, uid, ucount, username, track, title, time, @user_rank := if(@current_username = username, @user_rank:=@user_rank+1, 1) as user_rank, @current_username := username from (select tracks.id, tracks.uid, count(*) as ucount, users.username, views.track, tracks.title, tracks.time from tracks join views on views.track = tracks.id join users on users.idu = tracks.uid group by tracks.id, tracks.uid, users.username, views.track, tracks.title, tracks.time) user_counts -- initialize our variables join (select @user_rank := 0, @current_username := 'abcdefg') r order by username, ucount desc) dt where user_rank = 1 order by ucount desc, username
id uid count username track title time
55 567 3 TrainingPuppy 55 No love liers 2016-10-11 22:57:51
2 15 1 SewingButtons 2 Worf is in the air 2016-02-11 22:57:35
 hidden batch(es)