Top 3 by Year with a count


51 просмотра

1 ответ

572 Репутация автора

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|


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

Автор: Todd M Источник Размещён: 08.11.2017 10:52

Ответы (1)

1 плюс

865446 Репутация автора

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).

Автор: Gordon Linoff Размещён: 08.11.2017 11:46
Вопросы из категории :