How to display row or rows having MAX(value)?

Question

I am trying to display a row or multiple rows having the maximum salary, I have tried the following things. I want to display at least the name and the max(salary) but I am unable to do so.

I tried to execute this code snippet but it shows me the first row instead of showing the one which has the maximum salary.

select * from instructor
having max(salary);

I also tried

select name, max(salary) from instructor;

But this returned the name from the first row and the maximum salary value from the whole column.

Answer 1

If you are running Oracle, I would recommend a fetch clause:

select *
from instructor
order by salary desc
fetch first row with ties

An alternative uses a correlated subquery (this is supported in Oracle, and in any version of MySQL):

select *
from instructor
where salary = (select max(salary) from instructor)

Alternatively, you can use window functions (this requires MySQL 8.0)

select *
from (select i.*, rank() over(order by salary desc) rn from instructor) i
where rn = 1