Search code examples
sqloracle-databasesubquerytop-n

Get the name of the employee with the second highest salary

I have to get the name of employee with the second highest salary the table name from where I am fetching is emp. I know the query for second highest salary which is

select max(sal) 
from emp 
where sal < (select max(sal) from emp)

it works and it returns the right answer.

But I have to get the name of the employee as well. I simply tried

select name, max(sal) 
from emp 
where sal < (select max(sal) from emp)

I get this error:

ORA-00937: not a single-group group function

how can i remove the error in order to get the name and salary both. thank you to anyone who helps.

Solution

  • You can use

    select name,sal from emp where sal = (select max(sal) from emp where sal < (select max(sal) from emp));