CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Random guy
Random guy

Reputation: 923

ORA-00920: invalid relational operator while using IN operator

I have a table col where i have:

select * from offc.col;

enter image description here

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:

enter image description here

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?

Upvotes: 1

Views: 886

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50077

Perform an INNER JOIN with your subquery:

SELECT c.*
  FROM offc.col c
  INNER JOIN (SELECT dept_id, 
                     year, 
                     Max(marks) AS MAX_MARK
                FROM offc.col 
                GROUP BY dept_id, 
                         year) s
    ON s.DEPT_ID = c.DEPT_ID AND
       s.YEAR = c.YEAR AND
       s.MAX_MARK = c.MARKS
  ORDER BY c.DEPT_ID, c.YEAR

An INNER JOIN only returns rows where the join condition is satisfied so any rows in OFFC.COL which do not have the maximum value for MARKS for a particular DEPT_ID and YEAR will not be returned.

Upvotes: 0

Popeye
Popeye

Reputation: 35930

I would suggest to use dense_rank analytical function as it can return two department if they have same marks in same year.(your current logic is same as this)

Row_number will give you only one random record if two department have same marks in same year.

select *
from (
    select 
        c.*, 
        dense_rank() over(partition by dept_id, year order by marks desc nulls last) as dr
    from offc.col c
) x
where dr = 1
order by dept_id, year

Also, your query is correct, just remove order by from it.

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
);

Demo of error with order by and working fine without order by.

Cheers!!

Upvotes: 2

GMB
GMB

Reputation: 222702

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

Upvotes: 1

Related Questions