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

[Peter Brawley]

A CTE gives the most elegant solution ...

with
 a as (
  select left(date,4) as year, lens, count(*) N
  from tb_exifinfo
  group by year,lens
 ),
 b as (
  select year, max(N) as maxN
  from a
  group by year
 )
select a.year, a.lens, a.N
from a join b on a.year=b.year and a.N=b.maxN;