CODEWITHSUNDEEP
mysqlsqldatabase
Varun Teja
Varun Teja

Reputation: 523

Can we use aggregate function within aggregate function?

select max(salary)
from employ
where salary < ( select max(salary)
                 from employ
                 having count(max(salary)>salary)=3);

Here I am trying to find out 4th max salary using aggregate function in aggregate function.It is showing an error Invalid use of group function.

What is wrong in it?please help

thank you.

Upvotes: 0

Views: 1694

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

The easiest way is to just use limit:

select max(salary) as salary
from employ
order by salary desc
limit 3, 1;

Upvotes: 3

potashin
potashin

Reputation: 44581

No, you can't do that. Instead you can order by salary in the descending order, then limit your result to 4 records and retrieve the minimum one:

select min(salary)
from ( select max(salary) as salary
       from employ
       order by salary desc
       limit 4)

Upvotes: 2

Related Questions