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?
Best Answer
Setup the tables and some sample data:
Start with a (ANSI standard
group by
) query to obtain our counts:We'll call the above 'user_counts', and create a query that generates a ranking over
username
ordered byucount 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:And the results:
Some fiddles:
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
).