how to check the 3rd max salary from an employee table?
Answers were Sorted based on User's Feedback
Answer / akula
select min(sal) from(select distinct(sal) from emp order by sal desc) where rownum<=3;
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / dulal
Without Using MAX or MIN keyword
select TOP 1 EmpName, RevisedMinutes
from tblEncounter
where RevisedMinutes IN(select distinct Top 3
RevisedMinutes from tblEncounter order by RevisedMinutes
desc)
order by RevisedMinutes asc
Is This Answer Correct ? | 2 Yes | 2 No |
Answer / srinivas
select max(sal) from emp where sal<(select max(sal) from emp where sal<(select max(sal) from emp))
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / kotesh
select level,max(salary) from employee
where level=3
connect by prior salary>salary
group by level;
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / pavan ranga
select top 1 (salary),Name from customers
where salary not in (Select top 2 (Salary) from customers)
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / a.brahmam
select * from(select rownum r,sal from(select * from table name order by sal desc))
where r=3;
(or)
select * from(select rownum,sal from table name order by sal desc)
where rownum<=3
minus
select * from(select rownum,sal from table name order by sal desc)
where rownum<=2
(or)
select * from(select sal,dense_rank()over(order by sal desc)r from table name)
where r=3
Is This Answer Correct ? | 0 Yes | 0 No |
SELECT *
FROM(
SELECT SALARY, DEPARTMENT_ID,EMPLOYEE_ID, DENSE_RANK() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS RN
FROM EMPLOYEES
)
WHERE RN = 2
;
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / neeraj
SELECT TOP 1 * FROM [SELECT TOP 3 * FROM Emp_Salary
ORDER BY Salary DESC;]
ORDER BY Salary;
Is This Answer Correct ? | 3 Yes | 4 No |
Answer / akilis.org@hotmail.com
Let us Assume
Table Name=salary
Column Name=maxsal
select * from salary order by maxsal desc limit 2,1;
Enjoy the simple code :)
Is This Answer Correct ? | 1 Yes | 2 No |
Answer / kumar sumit
select max(sal) from emp where sal not in(select max(sal)
from emp where sal not in(select max(sal) from emp))
Is This Answer Correct ? | 0 Yes | 3 No |
what is sql and plsql
What is difference between Procedures and Functions ?
Which tcp/ip port does sql server run on? How can it be changed? : Sql dba
What is difference between nchar and nvarchar?
what is oracle database ? : Sql dba
What is dialect in sql?
Is sql a scripting language?
How can we overcome recursive triggers in SQL?
Types of cursors and explanation each of them ?
scope of exception handling in plsql
What is nosql vs sql?
Explain what is dbms?