Top 3 by Year with a count

Question

I have the following query

select year, name, count(*) as c from product 
group by year, name order by year, c desc limit 10

which is producing

+----+------------+------+
|year|name        |c     |
+----+------------+------+
|2007|           A|   913|
|2007|           J|   814|
|2007|           M|   565|
|2007|           C|   453|
|2007|           S|   414|

etc.

year column has 2007 through 2017.

How can I find the the top 3 name per year?

I've been trying with rank and dense_rank, but no luck.

I feel like I'm close, but just not there.

Database doesn't matter, I'm looking more for conceptual here than db specific. I can translate it as needed I suppose.

Thanks in advance

Are you sure your code above works? you are selecting three columns, one being an aggregate, you are grouping on two columns but the selected column "name" and group column "first_letter" are different. Most DBs I've worked with will complain that "name" isn't in your group by statement. — Sentinel, Nov 08 '17 at 22:59
@Sentinel - definitely MySQL, it doesn't enforce group by rules until the most recent versions. Todd - you will want to revist your group by line there as it's likely MySQL isn't doing what you intend (and if it is, it's accidental). I asked a previous question about top 20%, the logic will work for you here. https://stackoverflow.com/questions/38491171/top-20-percent-by-id-mysql If I'm correct you are in MySQL, please edit your question to specify MySQL...if you are in most other databases, this is really easy. — Twelfth, Nov 08 '17 at 23:06
Also, I'm decently sure (unless new versions change it) that MySQL does not support dense_rank and you have to fake it. Fortunately that faking has been asked and answered on stack. https://stackoverflow.com/questions/32494950/are-there-any-functions-in-mysql-like-dense-rank-and-row-number-like-oracle — Twelfth, Nov 08 '17 at 23:08

score 1 · Answer 1 · answered Nov 08 '17 at 23:46

The simplest way in MySQL is a bit of a hack. Assuming the names don't have commas:

select year, substring_index(group_concat(name order by cnt desc), ',', 3) as names
from (select year, name, count(*) as cnt
      from product 
      group by year, name
     ) t
group by year
order by year;

Note: This also has internal limits based on the default size of the group_concat() intermediate value. It should work fine on reasonable data sets (with dozens but not thousands of names per year).

By "first_letter", I assume you mean name or left(name, 1).

Top 3 by Year with a count

1 Answers1