Select the most frequent value of each year in the db

[Peter Nilsson]

Hi!
I have a database with the following table:
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| filename | char(18) | NO | PRI | | |
| iso | mediumint | YES | | NULL | |
| aperture | decimal(3,1) | YES | | NULL | |
| exposuretime | tinytext | YES | | NULL | |
| focal_length | smallint | YES | | NULL | |
| lens | tinytext | YES | | NULL | |
| date | date | NO | PRI | NULL | |
| time | time | NO | PRI | NULL | |
| model | tinytext | YES | | NULL | |
| flash | tinyint(1) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
10 rows in set (0.00 sec)

What I want to do is to select the most popular lens of each year.

I can do this select:
SELECT lens,COUNT(*) as max from tb_exifinfo where year(date) = 2018 group by lens order by max desc limit 1;
and it gives me:
+-----------------------------+-----+
| lens | max |
+-----------------------------+-----+
| Canon EF 24-70mm f/2.8L USM | 273 |
+-----------------------------+-----+

But that only gives my the most popular lens for one single year. If we look at the content so far when it comes to years:

SELECT year(date) from tb_exifinfo group by year(date) order by year(date);
+------------+
| year(date) |
+------------+
| 2007 |
| 2008 |
| 2009 |
| 2010 |
| 2011 |
| 2012 |
| 2013 |
| 2014 |
| 2015 |
| 2016 |
| 2017 |
| 2018 |
| 2019 |
| 2020 |
+------------+
14 rows in set (0.02 sec)

Is it possible to have one query with maybe some sort of subquery to get the most popular lens (string) for each and every year?

Best regards,

Peter