SQL - Greatest-n-per-group of the average

Question

I am having trouble with the following query. Lets say you have the following table of employees:

Example table EMPLOYEE:

IDEMP |    JOB    | SALARY | CONTENT 
------+-----------+--------+---------
  1   |   CLERK   |  1500  |   ...   
  2   |   CLERK   |  1000  |   ...   
  3   | PRESIDENT |  5000  |   ...   
  4   |  ANALYST  |  2000  |   ...   
  ...       ...       ...      ...

I am trying to obtain the JOB that has the highest average salary. I can easily obtain the average salary of each JOB with this subquery:

(select job, avg(salary) as AVERAGE 
 from emp
 group by job) temp

However, I don't know how I can obtain after this subquery the max(AVERAGE) with the associated JOB. I know that if I only needed the row of the employee with the highest salary, I could do it as explained here SQL select only rows with max value on a column, but when you do the 'average subquery' you lose the IDs of the employees.

Since for this given example there is only one PRESIDENT, and the job has the highest average salary, the output should be this:

   JOB    |  MAX_AVERAGE 
----------+---------------
PRESIDENT |     5000

EDIT: My solution...

Thanks for the help, you brought up new ideas. I'm finally using this one because it does not limit the results to one row.

select e.job, avg(e.salary) as AVERAGE 
 from EMPLOYEE e
 group by e.job
 having avg(e.salary) >= ALL(select avg(salary) from EMPLOYEE group by job)

Tag your question with the database that you use. – forpas Nov 01 '20 at 16:53 — forpas, Nov 01 '20 at 16:53
What if two jobs have the same maximum average salary? – Gordon Linoff Nov 01 '20 at 18:35 — Gordon Linoff, Nov 01 '20 at 18:35

score 2 · Answer 1 · answered Nov 01 '20 at 16:57

2

Sort your query by descending average and pick the top row:

select job, avg(salary) as AVERAGE 
 from EMPLOYEE
 group by job
 order by AVERAGE desc limit 1

See the demo.

answered Nov 01 '20 at 16:57

forpas

160,666
10
38
76

that's actually very simple and smart, I guess I don't have that much of experience to have thought about that! – deko Nov 01 '20 at 17:04

score 1 · Answer 2 · answered Nov 01 '20 at 16:52

1

You can try the below - DEMO

with cte as
(
select job,avg(salary) avg_sal
from t1 group by job
)
select * from
(
select job,avg_sal, row_number() over(order by avg_sal desc) as rn
from cte
)A where rn=1

answered Nov 01 '20 at 16:52

Fahmi

37,315
5
22
31

1

And I thought I knew SQL, damn – deko Nov 01 '20 at 17:04

deko · Accepted Answer · 2020-11-05T16:37:42.513

0

I've figured out one query that returns my desired result and is also more flexible than the previously ones posted since it can return multiple rows if it was the case:

select e.job, avg(e.salary) as AVERAGE 
 from EMPLOYEE e
 group by e.job
 having avg(e.salary) >= ALL(select avg(salary) from EMPLOYEE group by job)

edited Nov 05 '20 at 16:37

answered Nov 01 '20 at 19:59

deko

463
1
6
17

SQL - Greatest-n-per-group of the average

3 Answers3