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 are the sql commands?
If 100 tables are there in user_tables.I want to find in which table zero records are there with table name.Is it possible?
how to fetch alternate records from a table? : Sql dba
What does the hierarchical profiler does?
Explain alias in sql?
Is oracle and sql same?
Explain clause in sql?
how can we optimize or increase the speed of a mysql select query? : Sql dba
- Types of triggers - View - Dcl - Procedures, packages, functions - Metasolve - Can use Dcl in triggers - package case study - Cursor and its types - triggers schedule - Wrap - Why we are using fetch and for in cursor. difference?
what is the difference between to_char and to_date functions?
a. Can you delete data from a View. b. If Yes, can you delete it if there are multiple tables c. If No, can you delete if there is single source table which is joining.
What are the syntax and use of the coalesce function?