Owen.Wang Owen.Wang - 4 years ago 139
SQL Question

oracle order by missing right parenthesis

I need to find the maximum number of distinct employees
So my code is like:

select ProjName from Project where ProjId in (
select ProjId from EmpProject
order by count(ProjId) desc
group by ProjId
where rownum<=1
);


But it throw an error says missing right parenthesis.
So could anyone told me how to fix this?

Answer Source

You have incorrect syntax there. The order by clause comes at the end and where comes before group by. So your query becomes:

SELECT ProjName
FROM Project
WHERE ProjId IN
  (SELECT ProjId
  FROM EmpProject
  WHERE rownum<=1
  GROUP BY ProjId
  ORDER BY COUNT(ProjId) DESC
  );

The above isn't probably what you want, assuming you want to get top projId with max count.

In 11g or before, you can use ordering in subquery and then apply the rownum filter outside:

SELECT ProjName
FROM Project
WHERE ProjId IN
  (SELECT ProjId
  FROM
    (SELECT ProjId FROM EmpProject GROUP BY ProjId ORDER BY COUNT(ProjId) DESC
    )
  WHERE rownum<=1
  );

In 12c and above, You can use FETCH FIRST like this:

SELECT ProjName
FROM Project
WHERE ProjId IN
  (SELECT ProjId FROM EmpProject GROUP BY ProjId ORDER BY COUNT(ProjId) DESC
  FETCH FIRST 1 row only
  );
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download