Find maximum average in sql

Question

I am trying to get the maximum average salary and i am using this:

select max (avg_salary)
from (select name, avg(salary) AS avg_salary
  from employee
  group by name);

But is another way to get same result without using subquery?

possible duplicate of [How to find maximum avg](http://stackoverflow.com/questions/8050854/how-to-find-maximum-avg) — vhadalgi, Nov 20 '13 at 10:25
Not a duplicate. The solution to that question is the query posted above. — OGHaza, Jan 03 '14 at 16:34

score 2 · Accepted Answer · answered Nov 20 '13 at 10:20

2

SELECT AVG(salary)
FROM employee
GROUP BY name
ORDER BY AVG(salary) DESC
LIMIT 1

answered Nov 20 '13 at 10:20

fthiella

score 1 · Answer 2 · answered Nov 20 '13 at 10:19

1

One other option would be:

SELECT name, avg(salary) AS avg_salary
FROM employee
GROUP BY name
ORDER BY 2 DESC LIMIT 1;

answered Nov 20 '13 at 10:19

Filipe Silva

score 0 · Answer 3 · edited Nov 20 '13 at 11:01

0

Use this instead

SELECT name, 
       avg(salary) AS avg_salary   
FROM employee 
GROUP BY name 
ORDER BY DESC avg_salary 
      LIMIT 1

edited Nov 20 '13 at 11:01

Hawk

answered Nov 20 '13 at 10:19

M Shahzad Khan

score 0 · Answer 4 · answered Nov 20 '13 at 10:22

0

Try this... this sort desc of avg salary then apply limit

SELECT AVG(salary),name
FROM employee GROUP BY name
ORDER BY AVG(salary) DESC
LIMIT 1

answered Nov 20 '13 at 10:22

naveen goyal

score 0 · Answer 5 · edited Nov 20 '13 at 11:19

0

All employees:

SELECT name, 
       AVG(salary) AS avg_salary 
FROM employee 
GROUP BY name 
ORDER BY avg_salary DESC

Maximum of one employee:

SELECT name, 
AVG(salary) AS avg_salary 
FROM employee 
GROUP BY name 
ORDER BY avg_salary DESC 
LIMIT 1

edited Nov 20 '13 at 11:19

Hawk

answered Nov 20 '13 at 10:24

Justin Paul Paño

5 Answers5