MySQL group by multiple columns on multiple tables only one result per group

Question

I have three different tables and I need to count how many rows has a specific value (COUNT(track)) then group by tracks.id and sort by only one result per user (tracks.uid).

My three tables:

`users`
+--------+-------------------+
| `idu` | `username` | 
+--------+-------------------+
| 567 | 'TrainingPuppy' | 
| 687 | 'BathroomMakeover'|
| 45 | 'PoshNachos' | 
| 15 | 'SewingButtons' | 
+--------+-------------------+
`views`
+--------+------+---------+
| `id` | `by` | `track` |
+--------+------+---------+
| 1 | 5 | 55 | 
| 2 | 5 | 55 | 
| 3 | 67 | 55 | 
| 4 | 6 | 29 | 
| 5 | 125 | 2 | 
| 6 | 5 | 698 | 
| 7 | 5 | 698 | 
+--------+------+---------+
`tracks`
+--------+-------+-----------------------+---------------------+
| `id` | `uid` | `title` | `time` |
+--------+-------+-----------------------+---------------------+
| 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 |
+--------+-------+-----------------------+---------------------+

So I tried:

SELECT `views`.`track`, `tracks`.*, `users`.*, COUNT(`track`) as `count` 
FROM `views`,`tracks`,`users` 
WHERE `views`.`track` = `tracks`.`id` 
 AND `tracks`.`uid` = `users`.`idu` 
GROUP BY `tracks`.`uid` 
ORDER BY `count` 
DESC LIMIT 0, 20

RESULTS

+--------+-------+---------+---------------------+----------+-----------------------+---------------------+
| `id` | `uid` | `count` | `username` | `track` | `title` | `time` |
+--------+-------+---------+---------------------+----------+-----------------------+---------------------+
| 29 | 567 | 6 | 'TrainingPuppy' | 29 | 'Stargold' | 2016年10月11日 22:57:51 | 
| 2 | 15 | 1 | 'SewingButtons' | 2 | 'Worf is in the air' | 2016年02月11日 22:57:35 |
+--------+-------+---------+---------------------+----------+-----------------------+---------------------+

Instead I need to select only 1 track (the most counted in views table) of each user. Something like below:

+--------+-------+---------+---------------------+----------+---------------------+---------------------+
| `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 |
+--------+-------+---------+---------------------+----------+---------------------+---------------------+

How can I achieve this?

• Rick James: Improper use of GROUP BY; see the tag I added.
• NineCattoRules: @RickJames Could you please explain?
• Vérace: See here! Yet another reason why MySQL should not be used for serious work!
• markp-fuso: @NineCattoRules: ANSI std says all non-aggregate columns from the select list should show up in the group by clause and vice versa (ie, all columns in group by clause should show up as non-aggregates in the select list); failure to follow the ANSI std leaves you at the mercy of the RDBMS and how it chooses to process a non-ANSI group by ... usually leading to undesired results
• Rick James: @Vérace - a partial fix: As of 5.7.5, ONLY_FULL_GROUP_BY is enabled by default.

Answer 1

Setup the tables and some sample data:

drop table if exists users;
drop table if exists views;
drop table if exists tracks;
create table users
(idu int
,username varchar(30));
create table views
(id int
,`by` int
,track int);
create table tracks
(id int
,uid int
,title varchar(30)
,time datetime);
insert into users values
(567,'TrainingPuppy'),
(687,'BathroomMakeover'),
( 45,'PoshNachos'),
( 15,'SewingButtons');
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);
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');

Start with a (ANSI standard group by) query to obtain our counts:

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

We'll call the above 'user_counts', and create a query that generates a ranking over username ordered by ucount desc:

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 user_counts,
 -- initialize our variables
join (select @user_rank := 0, @current_username := 'abcdefg') r
order by username, ucount desc

Then wrap this in our final select, making sure to only display those rows with a ranking of '1' ... pulling it all together into a single query we have:

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

And the results:

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

Some fiddles:

• MySQL 5.6
• MySQL 5.7
• MariaDB 10.2

---

NOTE: If a given username has the same max(count) for more than one track/title, this solution will only display one record; to display multiple records would require a tweak to ensure said records receive the same ranking (ie, user_rank = 1).

• NineCattoRules: Thanks, this is brilliant...however I need to DESC LIMIT 0, 20 the ucount since I'm using an Ajax call request system with infinite scroll down.
• markp-fuso: ok, so add your limit 0,20 to the final query ... yes?
• NineCattoRules: It's also very nice that I can get how many records needed of each artist just using something like user_rank < 3 (max 2 tracks of the same artist)