ORA-00920: invalid relational operator while using IN operator

I have a table col where i have:

select * from offc.col;

I returned some data using query by year wise ans dept_id wise:

 SELECT dept_id, 
       year, 
       Max(marks) marks 
FROM   offc.col 
GROUP  BY dept_id, 
          year 
ORDER  BY dept_id, 
          year 

The data I got was:

Here there is no problem as my sql is running right.So, I needed to extract all the information of col table,So I used subquery as:

SELECT * 
FROM   offc.col 
WHERE  ( dept_id, year, marks ) IN (SELECT dept_id, 
                                           year, 
                                           Max(marks) marks 
                                    FROM   offc.col 
                                    GROUP  BY dept_id, 
                                              year 
                                    ORDER  BY dept_id, 
                                              year); 

But,I got error as:

ORA-00920: invalid relational operator 

i searched this error at other pages also,but I found them as bracket misplace error.But in my case,I dont know what is happening here?

Answer

Instead of aggregating, you can filter with a correlated subquery:

select c.*
from offc.col c
where marks = (
    select max(marks)
    from offc.col c1
    where c1.dept_id = c.dept_id and c1.year = c.year
)
order by dept_id, year 

An index on (dept_id, year, marks) would speed up this query.

Another option is to use window function row_number():

select *
from (
    select 
        c.*, 
        row_number() over(partition by dept_id, year order by marks desc) rn
    from offc.col c
) x
where rn = 1
order by dept_id, year 

---

If you do want to stick to aggregation, then you can join your subquery with the original table as follows:

select c.* 
from offc.col c
inner join (
    select dept_id, year, max(marks) marks 
    from offc.col 
    group  by dept_id, year 
) m 
    on m.dpt_id = c.dept_id
    and m.year = c.year
    and m.marks = m.marks