By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with PROJ (projno, pname, end_date) as (
select 1, 'PROJECT1', to_date('16/10/31', 'RR/MM/DD') from dual union all
select 2, 'PROJECT2', to_date('16/11/30', 'RR/MM/DD') from dual union all
select 3, 'PROJECT3', to_date('16/12/31', 'RR/MM/DD') from dual union all
select 4, 'PROJECT4', to_date('16/12/20', 'RR/MM/DD') from dual union all
select 5, 'PROJECT5', to_date('17/12/29', 'RR/MM/DD') from dual
)
select t.nr_proj, t.month, t.year
from (
SELECT COUNT(proj.projno) nr_proj
, extract(month from proj.end_date) month
, extract(year from proj.end_date) year
, max( COUNT(proj.projno) ) over(partition by extract(year from proj.end_date)) mx
FROM PROJ
GROUP BY extract(month from proj.end_date), extract(year from proj.end_date)
) t
where nr_proj = mx
;
| NR_PROJ | MONTH | YEAR |
|---|---|---|
| 2 | 12 | 2016 |
| 1 | 12 | 2017 |