Removing duplicate rows based on column value

Question

I have a the query below and it is working but some of the attributes for NRLG_SMAINT are not Y for the same attribute ROADLOG/HPMS and I would like to remove the duplicate row. I don't mind if any of the NRLG_SMAINT attributes are not Y, but if it is Y, I don't want that row to be showing for the same ROADLOG/HPMS attribute where there is a Y. The query is below:

select t.nrlg_dept_route || t.nrlg_dept_roadbed as roadlog,s.HPMS,t.nrlg_smaint
from TIS.TIS_NEW_ROADLOG t right join HPMS_DATA s
on t.nrlg_dept_route || t.nrlg_dept_roadbed = s.hpms
group by t.nrlg_dept_route || t.nrlg_dept_roadbed,s.HPMS,t.nrlg_smaint
order by 1

Here is a sample of the output so far:

                ROADLOG       HPMS      NRLG_SMAINT
          85    C001821N    C001821N    
          86    C001992N    C001992N    
          87    C005201N    C005201N    Y
          88    C005201N    C005201N    --- remove this row
          89    C005202E    C005202E    Y
          90    C005202E    C005202E    --- remove this row
          91    C005203N    C005203N    Y
          92    C005203N    C005203N    --- remove this row
          93    C005205N    C005205N    Y
          94    C005205N    C005205N    
          95    C005207S    C005207S    --- leave this row
          96    C005208N    C005208N    Y
          97    C005208N    C005208N    
          98    C005209N    C005209N    Y
          99    C005209N    C005209N    

Answer 1

I think you want to fix the aggregation:

select t.nrlg_dept_route || t.nrlg_dept_roadbed as roadlog,
       s.HPMS,
       max(t.nrlg_smaint) as nrlg_smaint
from  HPMS_DATA s left join
      TIS.TIS_NEW_ROADLOG t 
      on t.nrlg_dept_route || t.nrlg_dept_roadbed = s.hpms
group by t.nrlg_dept_route || t.nrlg_dept_roadbed, s.HPMS
order by 1