Interested to Buy Any Domain ? << Click Here >> for more details...
Write a query to find second highest salary of an employee.
- 72 Answers
- 130550 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 the difference or comparison between QTP and rational? and which tool is better to use?
in VSS, what is meant by check out and check in?
what are the Extension files in QTP?
Does automation replace manual testing?
what testing activity you may want to automate?
Tell us what you know about table-driven testing?
What testing activities you may want to automate?
What steps are needed in developing and running a software test?
WHAT IS TEST SCRIPT AND HOW DO YOU CREATE IT?
Describe some problem that you had with automating testing tool.
What is Sikuli?
can you ple give me Quality Center interview questions and answer
WinRunner 
