Interested to Buy Any Domain ? << Click Here >> for more details...
Write a query to find second highest salary of an employee.
- 72 Answers
- 128908 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 |
Hi every body.. Whn i install QC 9.2 in window xp sp2 it ll show the error msg as it will nt support. How can i install in this OS.. Plz plz help me and i installed IIS and oracle 8i.. Plz Plz help me for this one
what is aom and how to add object repository? i am very new to QTP please help me
How would you test your own element locator?
How to capture Dos Promote data in text file? in that how to find particular String ( window )? in VB scrpting
in VSS, what is meant by check out and check in?
Is web based application a 2 tier or 3 tier application.
When you will not automate testing?
what is best bug in your project?
How will you automate basic “login” functionality test cases for an application?
What is an automation testing framework?
Does automation testing have any disadvantages?
why we are moving to QTP instead of using winrunner? Does QTP support any other applications which Winrunner cannot support?
WinRunner 
