Top 3 by Year with a count
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
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