how to find nth highest salary
Answers were Sorted based on User's Feedback
Answer / saravanan
The below 2 queries will work
n - The nth highest salary
First query
------------------------
SELECT MAX(salary)
FROM
(SELECT salary
FROM emp
WHERE salary IS NOT NULL
ORDER BY salary DESC)
WHERE rownum < &n;
Second query
------------------------
SELECT DISTINCT(a.salary)
FROM emp a
WHERE &n =
(SELECT COUNT(DISTINCT(b.salary))
FROM emp b
WHERE a.salary <= b.salary)
;
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / parmesh m
select top 1 salary from Employee where salary in (
select top n salary from Employee
group by salary
having count(salary )< n+1
order by salary desc )
order by salary
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / kalai
SELECT * FROM EMP A WHERE 7=(SELECT COUNT(SAL) FROM EMP
WHERE A.SAL<SAL)
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / sanwar hudda
n th Highest salary
select salary from test t1 where n-1=(select COUNT (*) from
test
where salary > t1.salary )
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / sikindar
select * from emp where sal=(
SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal));
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / jaishree singh
select emp_name,salary from
(select emp_name,salary, row_number() over (order by salary)
rownumber from employee) E
where E.rownumber = 3
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / neeraj kumar
select top(1) Salary
from(select distinct top(n) Salary
from emp
order by Salary desc ) a
order by Salary
Note-you can find out any position salary through top(n).
n=1,2,3,4,5,6,7,8,9..................
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / eldho k scaria
There are two methods:(Suppose table name is EMPLOYEE,
Colomn name is SALARY, and N=6)
SELECT * FROM EMPLOYEE
WHERE SALARY IN
(SELECT TO 1 SALARY (SELECT DISTINCT TOP 6 SALARY FROM
EMPLOYEE ORDER BY SALARY DESC)
A ORDER BY SALARY)
or
SELECT * FROM EMPLOYEE E1
WHERE (6-1)=(SELECT COUNT (DISTINCT (E2.SALARY))
FROM EMPLOYEE E2
WHERE E2.SALARY>E1.SALARY)
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / anija
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC)
ORDER BY salary
Is This Answer Correct ? | 1 Yes | 0 No |
select salary from tablename t1
where n=(select count(*)from tablename t2
where t1.salary<=t2.salary)
Is This Answer Correct ? | 16 Yes | 16 No |
What is normalization? Explain its different types?
can i write function in stored procedure and stored procedure in function and nested procedure.Give one example for each question?
What are commonly used odbc functions in php?
Detail about the hardware which is supported by SQL server?
What is ms sql server triggers?
What is meant by indexing files?
Do you know spatial data types - geometry and geography in sql server 2008?
What is rank function?
What is the simplest way to create a new database in ms sql server?
What are the different types of collation sensitivity?
What are rows and columns?
Define DML and DCL statements?