non-aggregate, aggregate, group by and all that jazz

[Iliemoo]

Is it possible to write a single SQL query that include a aggregate function (say Max()) with Group By multiple non-aggregate columns and, here is the catch, include a Non-aggregate column that is not to be grouped? For example,
a table with column 1, 2, 3, and 4, I want to find the value of col4 when col3 is at MAX in each of grouping based on col1 and col2. I've read from various places that all columns in the SELECT list has to be included in GROUP BY, if the above is not possible, is there any other way to accomplish the same goal (other sql construct maybe)? Thank you!

 

[swampBoogie]

select * from t as q
 where c3 in (
    select max(c3) from t
     where c1 = q.c1
       and c2 = q.c2 )