Finding Maximum Part

Question

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