Given an employee table, how would you find out the second
highest salary?
Answers were Sorted based on User's Feedback
Answer / sowjanya
Select * from emp where sal=(select Max(sal) from emp where
sal<(select Max(Sal) from emp)
Is This Answer Correct ? | 7 Yes | 0 No |
Answer / trived
select * from emp
where n-1 = (select count(*) from emp e
where emp.salary<e.salary)
in place of n-1 you can keep zero for first hight salary
and 1 for second hightest salary.....etc
Is This Answer Correct ? | 9 Yes | 3 No |
Answer / rony sunny
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 2 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
Is This Answer Correct ? | 5 Yes | 1 No |
Answer / ravikumar
select ename,esal from
(select ename,esal from hsal
order by esal desc)
where rownum <=2
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / balindra sahani
select max(sal) from emp
where sal not in (select max(sal) from emp )
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / karna
select top 1 a.salary,* from emp a
where a.salary not in(select top (n-1) b.salary from emp b)
here n is the Nth values:if you want secong highest: keep n
as 2 so the query will become
select top 1 a.salary,* from emp a
where a.salary not in(select top 1 b.salary from emp b)
Is This Answer Correct ? | 3 Yes | 2 No |
Answer / balindra sahani
select max(sal) from emp
where sal not in (select max(sal) from emp order by sal desc )
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / mithilesh mishra
Select Max(sal) from emp where sal not in (select max(sal)
from emp)
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / vivek mohan singh
select salary from tablename order by salary desc limit 1,1;
Is This Answer Correct ? | 2 Yes | 3 No |
What is a self join? Explain it with an example?
How to create an external table.
How to replace not in with not exist?
What are the disadvantages of cursors? How can you avoid cursors?
Write the fastest query to find out how many rows exist in a table?
What is the database development process?
Explain about xml databases?
In which table collections errors are stored.
How to generate OIDS
agregator gives one to many records or many to one record
Explain about the hierarchical model of the database?
What is a join and explain different types of joins?