how to find nth highest salary
Answers were Sorted based on User's Feedback
Answer / seeman
select * from table_name t1 where (n-1)=
(select count(distinct(t2.column_name)) from table_name t2
where t2.column_name>t1.column_name)
Is This Answer Correct ? | 1 Yes | 1 No |
Answer / babu
select * from emp e where &num=(select count(distinct(sal))
from emp b where b.sal>=e.sal)
Is This Answer Correct ? | 1 Yes | 1 No |
Answer / arun
SELECT MAX(salary)FROM employee WHERE salary NOT IN
(SELECT TOP 1 salary FROM employee ORDER BY salary DESC)
Is This Answer Correct ? | 1 Yes | 1 No |
Answer / jiten
select top 1 salary from employee where salary in (select
top n salary from employee order by salary desc)
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / yogesh
select e2.salary
from emp e1
join emp e2
on e1.salary >= e2.salary
group by e2.salary
having count(e2.salary) = n
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / bhanu prakash
SELECT TOP 1 * FROM (SELECT TOP N * FROM EMPSAL ORDER BY ID DESC) A ORDER BY ID
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / harninder
select `salary` from test t1 where 0=(select count(*) from
test where t1.`salary`<`salary`)
Is This Answer Correct ? | 5 Yes | 6 No |
Answer / usha
SELECT EmpName, Salary
from
(
SELECT EmpName, Salary, Row_Number() OVER(ORDER BY SALARY
DESC) AS 'Salaries'
FROM #Employees
) emp
WHERE Salaries = n
n may be 2,3....
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / deven
<font color="Green" >This is long method but it works</color>
<font color="red" >step 1(this will make salary in
ascending order)</color>
<font color="blue" >create index Ind_sal on emp(salary)</color>
<font color="red" >step 2(to calculate Nth highest salary
where N=1,2,3,4.....)</color>
<font color="blue" >select distinct top N salary from emp
except
select distinct top N-1 salary from emp
</color>
<font color="red" >in some databases 2nd step may be</color>
<font color="blue" >
select distinct top N salary from emp
minus
select distinct top N-1 salary from emp</color>
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / suprotim agarwal
CREATE TABLE #Employees (EmpID int, EmpName varchar(10),
Salary int)
INSERT #Employees SELECT 1,'Tim',345345
INSERT #Employees SELECT 2,'Jill',76845
INSERT #Employees SELECT 3,'Kathy',234545
INSERT #Employees SELECT 4,'Jack',74564
INSERT #Employees SELECT 5,'Harry',56756456
INSERT #Employees SELECT 6,'Arnol',75675
INSERT #Employees SELECT 7,'Dave',76566
INSERT #Employees SELECT 8,'Zinnade',58776
INSERT #Employees SELECT 9,'Iflar',345567
INSERT #Employees SELECT 10,'Jimmy',76766
Highest Salary Of an Employee
SELECT EmpName, Salary
from
(
SELECT EmpName, Salary, Row_Number() OVER(ORDER BY SALARY
DESC) AS 'Salaries'
FROM #Employees
) emp
WHERE Salaries = 1
2nd highest salary
SELECT EmpName, Salary
from
(
SELECT EmpName, Salary, Row_Number() OVER(ORDER BY SALARY
DESC) AS 'Salaries'
FROM #Employees
) emp
WHERE Salaries = 2
Is This Answer Correct ? | 0 Yes | 1 No |
What happens if you are trying to access a schema not owned by you?
Does group by or order by come first?
Can you leave a union at any time?
what is statistics
About DTS usage ?
Well sometimes sp_reanmedb may not work you know because if some one is using the db it will not accept this command so what do you think you can do in such cases?
What are distributed partitioned views?
What is self contained sub query?
Whether the updations made to the views reflect the original records in the table
What is query cost in sql server?
How we create SQL Server 2005 Reporting Services ? Give me Sample
Explain filtered indexes?