select max value of a column only from certain rows

Question

 select name from myschema.table1 where 
 COL1 = 'A'and 
 COL2= 'B' and 
 LEVEL = (select max(LEVEL) from myschema.table1 where USERTYPE='C')

I know am querying against the maximum level in table, rather than a max level among rows with userType 'c'. I need to query only against ones with that usertype.

Answer 1

You are very close. You need a correlation clause:

select t.name
from myschema.table1 t
where COL1 = 'A'and COL2= 'B' and 
      LEVEL = (select max(t2.LEVEL)
               from myschema.table1 t2
               where t2.col1 = t.col1 and g2.col2 = t.col2 and t2.USERTYPE = 'C'
              );