Maximum of averages

I'm supposed to 开发者_如何学运维get every departments average wage and only show the department with the highest average wage. I figured out this query, but it doesn't work. Anyone got some ideas?

SELECT department, max(avg(wage))
FROM employees
GROUP BY department;

I get this error: ERROR at line 1: ORA-00937: not a single-group group function

Without CTEs you can do:

Select Z.Department, Z.AvgWage
From  (
        Select Department, Avg(Wage) AvgWage
        From Employees
        Group By Department
        ) As Z
Where AvgWage = (
                Select Max(Z1.AvgWage)
                From    (
                        Select Department, Avg(Wage) AvgWage
                        From Employees
                        Group By Department
                        )  Z1
                )

With CTEs you could do:

With AvgWages As
    (
    Select Department
        , Avg(Wage) AvgWage
        , Rank() Over( Order By Avg(Wage) Desc ) WageRank
    From Employees
    Group By Department
    )
Select Department, AvgWage, WageRank
From AvgWages
Where WageRank = 1

does this work:

select *
from
(
  SELECT 
      department
      , avg(wage) as ave_wage
  FROM employees 
  GROUP BY department
)x 
order by ave_wage desc 
where rownum < 2;

(disclaimer: completely untested, so I may have put the rownum bit in the wrong place)

Althogh the queries below show the same result as the other answers, it's nice to show users how it can be done as an alternative:

--Method 1 (Davek's select of 1st row over Order by) Brilliant!
--Method 2 (Thomas' where = sub-query result)
--Method 3 (Thomas' based on ranking)

--Method 4 (Inner join sub-queries)
select distinct a.department, a.wage from 
         (select distinct department, AVG(wage) as wage from employees group by department) as a
   inner join 
         (select Max(wage) as wage from
                (select distinct department, AVG(wage) as wage from employees group by department) as x) as b
   on a.wage = b.wage 
where a.wage = b.wage 

--Method 5 (AVG wage in (sub-query))
select distinct a.department, a.wage
from (select distinct department, AVG(wage) as wage from employees group by department) as a
Where a.wage in 
    (select Max(wage) as wage from
        (select distinct department, AVG(wage) as wage from employees group by department) as x)

Looking forward to seeing a custom function for this select also :)

By Googling...

Cause: A SELECT list cannot include both a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, and an individual column expression, unless the individual column expression is included in a GROUP BY clause.

Action: Drop either the group function or the individual column expression from the SELECT list or add a GROUP BY clause that includes all individual column expressions listed.