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)
;

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

Answer / kalai

SELECT * FROM EMP A WHERE 7=(SELECT COUNT(SAL) FROM EMP
WHERE A.SAL<SAL)

Answer / sanwar hudda

n th Highest salary

select salary from test t1 where n-1=(select COUNT (*) from
test

where salary > t1.salary )

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));

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

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..................

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)

Answer / anija

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC)
ORDER BY salary

Answer / elumalai.k

select salary from tablename t1
where n=(select count(*)from tablename t2
where t1.salary<=t2.salary)

What are the differences between ms sql server & oracle?

0 Answers  

Where do we generally create INDEX?

1 Answers   Ernst Young, Thomson Reuters,

How to change a login name in ms sql server?

0 Answers  

Can Somebody tell me the difference between Clustered & Non- Clustered Index??

5 Answers   Progressive,

two tables with table name ship(name,year) and battle (name,year),how to find the latest added year in the table ship

1 Answers  

Differentiate between a local and a global temporary table?

0 Answers  

How to write character string constants or literals in ms sql server?

0 Answers  

what is unique and xaml nonclustered index

0 Answers  

is there more the two primary key in a single table?

26 Answers   Eforce, Systematix,

What are the properties and different types of sub-queries?

0 Answers  

What is store procedure?

0 Answers  

when we use function instead of procedure? plz tell me the situation with ex?

3 Answers   IBM,