Finding Maximum Part-CodePudding

I'm Having tables with columns in parentheses like this:

Employee (Empno, Empname, City)
Project (Pno, Pname)
Part (Partno, Partname, Color)
Use (Pno, Partno)
Works (Empno, Pno)

I'm Using ORACLE 11g Express Edition

My Query:

SELECT *
  FROM (SELECT p.Pname AS Project_name
          FROM (SELECT COUNT(Color) AS colorPart, U.Pno, P.Color
                  FROM Part P
                  JOIN Use u
                    ON u.Partno = p.Partno
                   and p.Color = 'Red'
                 GROUP BY Color, Pno
                 ORDER BY colorPart DESC)
         WHERE rownum = 1) AS Final
  JOIN Project p
    ON Final.Pno = p.Pno;

Here I'm getting the error: ORA-00933: SQL command not properly ended

CodePudding user response：

You can rewrite the current query as

SELECT p.Pname AS Project_name
  FROM (SELECT Pno
          FROM (SELECT Pno, COUNT(Color) AS ColorPart
                  FROM Part P
                  JOIN Use u
                    ON u.Partno = p.Partno
                   AND p.Color = 'Red'
                 GROUP BY Color, Pno
                 ORDER BY ColorPart DESC)
         WHERE rownum = 1) Final
  JOIN Project p
    ON Final.Pno = p.Pno

where

AS preceding the alias Final should be removed, since aliasing a subquery is not allowed in Oracle DB unlike to some other DBMS
p.Pname should be in the outermost query