Write a query to find second highest salary of an employee.
- 72 Answers
- 108667 Views
- ABC, Cognizant, MegaSoft, Netwin Infosolutions, Nippon, Polaris, Satyam, SGS, TCS, Verisoft, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / ashish
select salary from
(select dense_rank() over(order by salary desc) as highest, salary from employee)as y
where highest=2
: replace 2 with n.... it will give nth highest salary
: rank() will work correctly only if all the salaries in employee table are distinct.
: dense_rank() will work correctly, does'nt matter salaries are distinct or NOT.
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / shahnawaz
please check this one .
first rank the salary coloum and then find nth heigest
salary
use rank function
select * from
(select a.*, rank()over(order by salary desc) rn from
emp_shah a)
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / prashant srivastava
**Exact answer to the question**
SELECT MIN(e1.Salary) FROM emp e1 WHERE e1.Salary IN
(Select e2.Salary from emp e2 ORDER BY e2.Salary DESC FETCH
FIRST 2 ROWS ONLY)
**Generic Solution**
SELECT MIN(e1.Salary) FROM emp e1 WHERE e1.Salary IN
(Select e2.Salary from emp e2 ORDER BY e2.Salary DESC FETCH
FIRST n ROWS ONLY)
Just replace n as the asked rank of the salary. For instance
If 29th highest salary is asked then replace n with 29..
simple...isn't it? :):)
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / raaghav
Try this query
===================
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
where n > 1 '(n is always greater than one)
E.g.:-
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 6 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / abhishek
SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT
MAX(SALARY) FROM EMPLOYEE)
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / ayyappa
Select max(esal) from emp where esal < (Select max
(esal) from emp)
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / jia
select * from employee orderby salary DESC
Limit 1,1
| Is This Answer Correct ? | 12 Yes | 12 No |
Answer / rk
select sal from
(select rank() OVER (order by sal desc) sno ,sal from emp)
y
where sno= n
here
y : is the alias name for (select rank() OVER (order by sal
desc) sno ,sal from emp)
n : is the number ie if u want second highest sal then 'n'
value will be 2. for third highest n= 3...
| Is This Answer Correct ? | 5 Yes | 5 No |
Answer / jawad humayun
SELECT MIN(salary)
FROM employ
WHERE salary IN (SELECT salary
FROM employ
ORDER BY salary DESC
LIMIT 2)
This querry will give the 2nd highest salary
to have nth highest salary replace 'LIMIT 2' by 'LIMIT n'
| Is This Answer Correct ? | 2 Yes | 2 No |
Answer / barochia dharmesh
select Income from (
select Rank() over(order by Income desc) topRecord, Income
from (select distinct Income from tblName) tbl ) tbl2
where topRecord = 2
| Is This Answer Correct ? | 1 Yes | 1 No |
What is an automated test script?
In realtime what is the Testing Process especially in automation plz its very urgent
what are the steps followed in unit testing, system testing, integration testing
Tell me an example of the languages supported by webdriver?
What are the frameworks available in rc?
What type problems faced in Recording the Apllication through WinRunner and QTP?
What types of scripting techniques for test automation do you know?
write a programme to insert into elements in array?n finding duplicate characters in given sting?
Hi friends, can anybody tell me what all the testing tools that persistant uses and what is the selection process for an fellow having 2yrs workexp on testing. Can anybody also help me out what is the minimum package that persistant is providing for 2yr workexp guy.
Hi Can anyone tell me the site wherefrom I can Install latest version of QTP on my PC?
Name some of the commonly used automation testing tools that are used for non-functional automation.
Define behavioral testing
WinRunner 
