Interested to Buy Any Domain ? << Click Here >> for more details...
Write a query to find second highest salary of an employee.
- 72 Answers
- 126074 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 |
Tell me an example of the languages supported by webdriver?
Which is the best testing institute for Selenium training in Hyderabad.
Give some examples of priority – severity defect levels.
can anybody tell me " which automation framework u r using?" some interviewer asked this quesion? pls give me answer
What is test driven development?
what is the difference between Batch test runner and framework ?
you have same applications (same server configaration)A&B you are doing nonfunctionality testing . what they differ in?
what is the difference between winrunner,loadrunner,qtp,silktest,rational robo?
Where can i get professional course on web services testing in hyderabad or bangalore
When you will not automate testing?
How to Rcognize the Objects in the Web Page Dialog Using QTP
How will you choose a tool for test automation?
WinRunner 
