-1

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.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Adnan Ahmed
  • 29
  • 9

3 Answers3

1

You can use

select name,sal from emp where sal = (select max(sal) from emp where sal < (select max(sal) from emp));
Abito Prakash
  • 4,368
  • 2
  • 13
  • 26
  • 1
    thanks a lot sir. Can u explain me the error please????? – Adnan Ahmed Sep 15 '19 at 07:25
  • Sure. The problem was A SELECT cannot have both a group function, such as AVG, COUNT, MAX, MIN, etc and an individual column expression like name or salary. In such cases you can use an individual column expression if it is included in a GROUP BY clause. The error is saying that you tried to execute a SELECT statement that needed a GROUP BY clause without having the GROUP BY clause. Hope this helps :) – Abito Prakash Sep 15 '19 at 07:45
0

use this : 

with cte (
select ROW_NUMBER() over(order by sal desc)rnum ,name,sal from emp )
select * from cte where rnum = 2
nimajv
  • 423
  • 3
  • 11
0

You can get this easily with a window function. Try something like this:

SELECT name, sal
FROM emp
QUALIFY RANK OVER(ORDER BY sal DESC) = 2

This will order your rows by Salary and then give each row a ranking. Then it will return the rows with ranking = 2.

If you want to ensure you only get one row back, change RANK to ROW_NUMBER.

ravioli
  • 3,749
  • 3
  • 14
  • 28