create view test as
select DIAGNOSIS_CODE_1, count(DIAGNOSIS_CODE_1) as r1, STAY_INDICATOR
from 6339_dataset_1
where STAY_INDICATOR='L'
group by DIAGNOSIS_CODE_1;

select DIAGNOSIS_CODE_1, max(r1) from test;