Find the Maximum from a Count in SQL

Question

I've a database of musical tracks, and I'm trying to find which genre has the most tracks. Genre is stored in a Work table, with each track in the Track table having a field for which Work it belongs to.

This query correctly returns the genres and how many tracks they each have:

SELECT w.genre
     , COUNT(t.name) [numTracks] 
  FROM Track t
  LEFT 
  JOIN Work w 
    ON t.workName = w.name 
  LEFT 
  JOIN AlbumContains ta
    ON ta.workName = t.workName  
 GROUP 
    BY w.genre

But I can't find a way to just take whichever genre (or multiple genres) has the highest numTracks from that result.

Thanks in advance!

@Strawberry It's MS SQL Server, I'm not sure how that tag got added. — cogm, Dec 08 '14 at 11:40
I believe M$SQL uses TOP but not using it, I don't really know. — Strawberry, Dec 08 '14 at 11:43

Saharsh Shah · Accepted Answer · 2014-12-08T11:45:24.460

1

Try this:

MySQL

SELECT w.genre, COUNT(t.name) AS numTracks
FROM work w 
LEFT JOIN Track t ON t.workName = w.name 
GROUP BY w.genre
ORDER BY COUNT(t.name) DESC 
LIMIT 1

SQL SERVER

SELECT TOP 1 w.genre, COUNT(t.name) AS numTracks
FROM work w 
LEFT JOIN Track t ON t.workName = w.name 
GROUP BY w.genre
ORDER BY COUNT(t.name) DESC

edited Dec 08 '14 at 11:45

answered Dec 08 '14 at 11:36

Saharsh Shah

28,687
8
48
83

That's just returning the list of genres and how many tracks are in each – cogm Dec 08 '14 at 11:38
I'm trying to find which genre (or genres) has the most tracks – cogm Dec 08 '14 at 11:42
I get an "Incorrect syntax near 'LIMIT'." error with that. And would that return all the genres with the most tracks, or just the top one? – cogm Dec 08 '14 at 11:48

score 0 · Answer 2 · answered Dec 08 '14 at 11:35

0

select * from (SELECT Work.genre, count(Track.name) as numTracks FROM Track LEFT JOIN Work ON Track.workName=Work.name LEFT JOIN AlbumContains ON Track.workName=AlbumContains.workName GROUP BY Work.genre)alias order by aias. numTracks desc limit 1

answered Dec 08 '14 at 11:35

Ronak Shah

1,539
2
13
20

score 0 · Answer 3 · edited May 23 '17 at 10:33

I think your problem is not exactly, but somehow similar to this problem: SQL Select only rows with Max Value on a Column

If you want to have all genres with the most tracks and not only one (as far as I understand, you want that in your question), then you might have to join your query again, or maybe use a subselect like this (MySQL):

SELECT Work.genre, count(Track.name) as numTracks
  FROM Track
  LEFT JOIN Work ON Track.workName=Work.name
  GROUP BY Work.genre
  HAVING numTracks=
  (SELECT count(Track.name) as numTracks
    FROM Track
    LEFT JOIN Work ON Track.workName=Work.name
    GROUP BY Work.genre
    ORDER BY numTracks DESC
    LIMIT 1);

Or this in MS SQL:

SELECT Work.genre, count(Track.name) as numTracks
  FROM Track
  LEFT JOIN Work ON Track.workName=Work.name
  GROUP BY Work.genre
  HAVING count(Track.name) =
  (SELECT TOP(1) count(Track.name) as numTracks2
    FROM Track
    LEFT JOIN Work ON Track.workName=Work.name
    GROUP BY Work.genre
    ORDER BY numTracks2 DESC
  );

This looks rather awkward, but probably the server optimizes the subquery. However, for a solution with better performance, you may consider using a cursor instead.

Find the Maximum from a Count in SQL

3 Answers3