ora-00907 missing right parenthesis in subquery

Question

oracle shows error: ora-00907 missing right parenthesis

SELECT EQUIPMENT_ID
    FROM IRRI_EQUIPMENT_STATUS_CHANGE
   WHERE ID IN(SELECT MAX (ID)
                    FROM IRRI_EQUIPMENT_STATUS_CHANGE
                   WHERE STAUS_YEAR_MONTH <= 105
                GROUP BY EQUIPMENT_ID, STAUS_YEAR_MONTH
                ORDER BY MAX (ID) ASC, STAUS_YEAR_MONTH DESC)
ORDER BY EQUIPMENT_ID

Answer 1

ORDER BY in IN clause query is not required and also it serves no purpose and throws error.

Your query should look like this:

SELECT EQUIPMENT_ID
    FROM IRRI_EQUIPMENT_STATUS_CHANGE
   WHERE ID IN(SELECT MAX (ID)
                    FROM IRRI_EQUIPMENT_STATUS_CHANGE
                   WHERE STAUS_YEAR_MONTH <= 105
                GROUP BY EQUIPMENT_ID, STAUS_YEAR_MONTH)
ORDER BY EQUIPMENT_ID

Cheers!!