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 |
Can we call stored procedure in trigger?
Tell me about joins in database system and explain each in detail.
0 Answers Axtria, ITC Indian Tobacco Company,
How do I compare two values when one value(data type) is char and the other is an integer?
What is table level trigger?
If any possiable to use 2 primary key in single table? How many primary key & foreign key used in each table(min & max)?
what is sql injection in sql server?
What are the aggregate and scalar functions?
What is the purpose of the tempdb database?
How do you implement session management in SQL Server mode?
What is difference between unique and primary key?
What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?
Explain different types of index?