How to select top 5 most frequent value in a column

Question

I want to select the top 5 most frequent albums from a database, using this database: http://www.sqlitetutorial.net/tryit/query/sqlite-inner-join/#3

with this query:

SELECT
  albums.title AS Album,
  artists.name AS Artist,
  COUNT(albums.title ) as TitleCount
FROM tracks
INNER JOIN albums ON albums.albumid = tracks.albumid
INNER JOIN artists ON artists.artistid = albums.artistid
GROUP BY 
  albums.albumid 
ORDER BY 
  TitleCount DESC

Limit 5 is not a good solution, as in my case, there are 2 albums with 25 titles, what I want is the top 5 by titleCount value. It seems like limit doesn't take COUNT, but integers

expected result:

|Album               |Artist       |TitleCount|
|Greatest Hits       |Lenny Kravitz|57        |  
|Minha Historia      |Chico Buarque|34        |
|Unplugged           |Eric Clapton |30        |
|Lost, Season 3      |Lost         |26        |
|Lost, Season 1      |Lost         |25        |
|The Office, Season 3|The Office   |25        |

ps: this might be a duplicate of SQL - Most frequent value in column of joined tables, but I can't apply it to mine

You typically GROUP BY the columns you select, except those who are arguments to set functions. I.e. try `GROUP BY albums.title, artists.name`. — jarlh, Oct 25 '18 at 08:25
Are you using MySQL or SQLite? Just adding `LIMIT 5` to the end of your query should work. — Tim Biegeleisen, Oct 25 '18 at 08:28
@Tim Biegeleisen: `LIMIT` doesn't consider ties in either of the two DBMS. The result with `LIMIT 5` would be five rows instead of the expected six. — Thorsten Kettner, Oct 25 '18 at 08:48
@ThorstenKettner Good on you for picking up on that requirement. I saw the six rows and shrugged it off as a typo or copying error. Nice trick finding the 5 distinct highest counts. — Tim Biegeleisen, Oct 25 '18 at 09:01

Thorsten Kettner · Accepted Answer · 2018-10-25T08:38:56.387

MySQL's and SQLite's LIMIT clauses both lack a WITH TIES option, which is what you'd need here. So use a subquery instead: Select the five greatest numbers of tracks (which is a rare case where you actually combine GROUP BY with DISTINCT - GROUP BY to get counts per album, DISTINCT to get the five highes different counts), then select the albums having as many tracks. As this is about an aggregation result, this belongs in the HAVING clause:

SELECT
  albums.title AS Album,
  artists.name AS Artist,
  COUNT(*) as TitleCount
FROM tracks
INNER JOIN albums ON albums.albumid = tracks.albumid
INNER JOIN artists ON artists.artistid = albums.artistid
GROUP BY albums.albumid 
HAVING COUNT(*) IN
(
  SELECT DISTINCT COUNT(*)
  FROM tracks
  GROUP BY albumid
  ORDER BY count(*) DESC
  LIMIT 5
)
ORDER BY TitleCount DESC;

user2042214 · Answer 2 · 2018-10-25T08:51:21.410

0

Use below code

    SELECT
  albums.title AS Album,
  artists.name AS Artist,
  COUNT(*) as TitleCount
FROM tracks
INNER JOIN albums ON albums.albumid = tracks.albumid
INNER JOIN artists ON artists.artistid = albums.artistid
GROUP BY albums.albumid 
HAVING COUNT(*) IN
(
  SELECT DISTINCT COUNT(*)
  FROM tracks
  GROUP BY albumid
  ORDER BY count(*) DESC

)
ORDER BY TitleCount DESC LIMIT 5;

edited Oct 25 '18 at 08:51

answered Oct 25 '18 at 08:35

user2042214

165
9

if you do group by album instead of albumid then it will be give the exact out put – user2042214 Oct 25 '18 at 08:40
No. Grouping by album_id gets one result row per album. One album has exactly one title and one artist in this database. So yours is just another way of writing the same query. There are some DBMS, though, that don't comply with the SQL standard and require a full list of columns, just the way you are showing. Anyway, the problem is with ties. Your query results in exactly five rows, while the OP wants six for their example. – Thorsten Kettner Oct 25 '18 at 08:44
It seems like you copy pasted Thorsten Kettner's solution (like there is an extra space where the LIMIT 5 was originally), and placed LIMIT 5 outside of HAVING, which resulted in a maximum of 5 rows chokepoint (which works just like without the HAVING part) – Lucy Horvath Oct 25 '18 at 09:30

score 0 · Answer 3 · answered Oct 25 '18 at 13:30

While the other answer works fine, if you're using sqlite 3.25 or newer (Or I think MySQL 8), this can be done in a simpler fashion using the dense_rank() window function:

WITH rankings(Album, Artist, TitleCount, ranking) AS (
  SELECT al.title, ar.name, count(al.title)
       , dense_rank() OVER (ORDER BY count(al.title) DESC)
  FROM tracks AS t
  INNER JOIN albums AS al ON al.albumid = t.albumid
  INNER JOIN artists AS ar ON ar.artistid = al.artistid
  GROUP BY al.albumid)
SELECT Album, Artist, TitleCount
FROM rankings
WHERE ranking <= 5
ORDER BY ranking;

How to select top 5 most frequent value in a column

3 Answers3