how to find the second highest salary from emp table?
Answers were Sorted based on User's Feedback
Answer / neil
select * from (select sal,deptno from emp a where
sal in (select distinct(b.sal) from emp b
where a.deptno=b.deptno and rownum<4)
order by deptno,sal desc )
minus
select * from (select sal,deptno from emp a where
sal in (select distinct(b.sal) from emp b
where a.deptno=b.deptno and rownum<3)
order by deptno,sal desc )
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / praveenkumar
SELECT salary FROM EMPtable
WHERE salary NOT IN ( SELECT MAX(salary)
FROM EMPtable)
ORDER BY salary DESC
FETCH FIRST ROW ONLY
---
We have verified and its working
fine.
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / arun
select distinct (a.salary) from employees a
where &N = (select count (distinct(b.salary))
from employees b where a.salary <= b.salary);
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / manvar prajesh
select top 1 name,salary from emp where salary<(select
max(salary) from emp)order by salary desc
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / barochia dharmesh
You should use rank query with order by and give where your
search criteria.
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / vikas
select * from(select e.*,row_number() over (order by
e.salary desc)rn from Employee e)where rn between 1 and 2
order by rn
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / ankit khanijau
Select salary from emp
where MAX(Select salary from emp where salary != MAX(salary))
Is This Answer Correct ? | 1 Yes | 1 No |
Answer / abir dutta
select min(empsal)
from (select * from sal order by empsal desc)
where rownum <=2
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / monika
Select salary from emp order by salary desc LIMIT 1,1;
Is This Answer Correct ? | 2 Yes | 2 No |
Answer / bhoopendra vishwakarma
select salary max(salary) from emp limit1 offset1
Is This Answer Correct ? | 0 Yes | 0 No |
what is a sub query?how will you calculate working days in a month using sub query?
Can we insert data into materialized view?
Explain what is rdbms?
what is user defined functions? : Sql dba
counting the no.of characters occurs in a string by using pl/sql function
What is the difference between pl and sql?
Why is there a need for sqlcode and sqlerrm variables?
Which are sql * plus commands?
What are different types of tables in sql?
what is a control file ? : Sql dba
IF EMP HAS 2 ROWS,DEPT HAS 4 ROWS.WHATS THE RESULT OF SELECT * FROM EMP,DEPT;
I need a function for a train ticket reservation please answer it thanks in advance