SQL nesting question

[rflcptr]

This was part of a school assignment, but it's been turned in. The professor only required the correct results (in Oracle 11g), but my query's been bugging me. I don't think it's very good because of the redundancy, but haven't been able to nail removing it.

The problem was to find the ID(s) of the project with the highest average salary of its employees, given the relations:

Employees (EmpID, Rank, Salary, Title)
Projects (ProjID, Type, Location, Budget)
Works (EmpID, ProjID, Pct)

I originally came up with:

SELECT ProjID
FROM
(
   SELECT ProjID, AVG(Salary) AS avgsal
   FROM Works, Employees
   WHERE Works.EmpID = Employees.EmpID
   GROUP BY ProjID
) Temp
WHERE Temp.avgsal = 
(
   SELECT MAX(avgsal)
   FROM
   (
      SELECT ProjID, AVG(Salary) AS avgsal
      FROM Works, Employees
      WHERE Works.EmpID = Employees.EmpID
      GROUP BY ProjID
   )
);

Similarly under MS SQL (2008 R2):

SELECT ProjID
FROM
(
   SELECT ProjID, AVG(Salary) AS avgsal
   FROM Works, Employees
   WHERE Works.EmpID = Employees.EmpID
   GROUP BY ProjID
) AS Temp
WHERE Temp.avgsal = 
(
   SELECT MAX(avgsal)
   FROM
   (
      SELECT ProjID, AVG(Salary) AS avgsal
      FROM Works, Employees
      WHERE Works.EmpID = Employees.EmpID
      GROUP BY ProjID
   ) AS Temp
);

I tried replacing the repeat of the AVG() aggregation with "Temp" without luck. Embedding the MAX() in the outer FROM isn't going to do anything useful, as it implies grouping on an already grouped table. I don't think I understand the scope of intermediate relations in regards to nested queries, either.

 

[ameoba]

Look into using a WITH clause - it's an Oracle SQL extension. It'll let you write that subquery once & give it a name so that you can reference it in multiple places later.

 

[rflcptr]

Thanks for the help! I'll post back in a bit.