Re: Select the most frequent value of each year in the db

[Peter Nilsson]

Sorry but cannot get my head around this one. Maybe a select of a select can solve the problem?

I have the following query and result:
mysql> SELECT lens,COUNT(*), year(date) from tb_exifinfo where year(date) group by lens,year(date);
+-----------------------------------+----------+------------+
| lens | COUNT(*) | year(date) |
+-----------------------------------+----------+------------+
| Sigma 18-50mm f/2.8 Macro | 495 | 2012 |
| Canon EF 70-200mm f/4L USM | 343 | 2010 |
| Sigma 18-50mm f/2.8 Macro | 615 | 2010 |
| Sigma 18-50mm f/2.8 Macro | 444 | 2013 |
| Sigma 10-20mm f/3.5 EX DC HSM | 223 | 2013 |
| Canon EF 70-200mm f/4L USM | 296 | 2013 |
| Sigma 18-50mm f/2.8 Macro | 990 | 2011 |
| Canon EF 70-200mm f/4L USM | 426 | 2011 |
| Canon EF 70-200mm f/4L USM + 1.4x | 92 | 2013 |
| Sigma 18-50mm f/2.8 Macro | 405 | 2014 |
| Sigma 10-20mm f/3.5 EX DC HSM | 277 | 2014 |
| Canon EF 100mm f/2.8 Macro USM | 128 | 2014 |
| Canon EF 70-200mm f/4L USM | 61 | 2012 |
| Canon EF 24-70mm f/2.8L USM | 574 | 2014 |
| Canon EF 70-200mm f/4L USM | 135 | 2014 |
| Canon EF 24-70mm f/2.8L USM | 486 | 2015 |
| Sigma 10-20mm f/3.5 EX DC HSM | 148 | 2015 |
| Canon EF 100mm f/2.8 Macro USM | 21 | 2015 |
| Canon EF 24-70mm f/2.8L USM | 429 | 2016 |
| Sigma 10-20mm f/3.5 EX DC HSM | 139 | 2016 |
| Canon EF 70-200mm f/4L USM | 322 | 2016 |
| Canon EF 24-70mm f/2.8L USM | 764 | 2017 |
| Sigma 10-20mm f/3.5 EX DC HSM | 32 | 2017 |
| Sigma 18-50mm f/2.8 Macro | 377 | 2007 |
| Canon EF 70-200mm f/4L USM | 74 | 2017 |
| Canon EF 100mm f/2.8 Macro USM | 35 | 2017 |
| Sigma 18-50mm f/2.8 Macro | 913 | 2008 |
| Canon EF 24-70mm f/2.8L USM | 273 | 2018 |
| Sigma 10-20mm f/3.5 EX DC HSM | 23 | 2018 |
| Canon EF 70-200mm f/4L USM | 72 | 2008 |
| Canon EF 70-200mm f/4L USM | 265 | 2018 |
| Canon EF 135mm f/2L USM | 8 | 2020 |
| Canon EF 70-200mm f/4L USM | 83 | 2019 |
| Canon EF 24-70mm f/2.8L USM | 180 | 2019 |
| Sigma 18-50mm f/2.8 Macro | 820 | 2009 |
| Canon EF 70-200mm f/4L USM + 1.4x | 111 | 2009 |
| Sigma 10-20mm f/3.5 EX DC HSM | 63 | 2019 |
| Sigma 10-20mm f/3.5 EX DC HSM | 35 | 2020 |
| Canon EF 70-200mm f/4L USM | 256 | 2009 |
| Canon EF 24-70mm f/2.8L USM | 13 | 2020 |
| Canon EF 16-35mm f/4L IS USM | 78 | 2020 |
| Canon EF 100mm f/2.8 Macro USM | 16 | 2020 |
| Canon EF 50mm f/1.4 USM | 100 | 2020 |
| Canon EF 70-200mm f/4L USM | 18 | 2020 |
+-----------------------------------+----------+------------+
44 rows in set (0.03 sec)

How can I make a select of this that takes the string with the highest value of each year?