Answer / himanshu

Just replace the N with required number:


SELECT empid, empname, empsalary FROM emp e
WHERE (SELECT COUNT(DISTINCT(e2.empsalary))
FROM emp e2
WHERE e2.empsalary >=
e.empsalary ) = N

ex: 4th highest

SELECT empid, empname, empsalary FROM emp e
WHERE (SELECT COUNT(DISTINCT(e2.empsalary))
FROM emp e2
WHERE e2.empsalary >=
e.empsalary ) = 4

Answer / aravinda

try with this query you will get desired position salary.


Select Salary From Employees a Where 1=(
Select Count(Distinct Salary) From Employees b
Where a.Salary <= b.Salary );

just replace where clause number for desired position. ,2,3,4

Answer / sanchit dubey

DECLARE @SQL VARCHAR(2000), @N INT
SET @N = 5
SET @N = @N - 1

SET @sql = 'select top 1 salary from ABC where salary not
in ( SELECT TOP ' + CAST(@n AS VARCHAR(100)) + ' salary
FROM ABC order by salary desc ) ' + ' order by salary desc '

SELECT @SQL
EXEC @SQL

Answer / avinash

declare @n;
set @n = N
select * from tbl
where @n in (
select dense_rank over (order by salary) as rank_sal
from tbl )

Answer / harshal

Below query is written assuming table "employee" has a column called "salary".
select salary from (select salary from (select distinct(salary) from employee order by salary desc)where rownum<n) order by salary asc where rownum<1;

Answer / yoursguna@gmail.com

SELECT * FROM `salary_table` group by `salary` order by
`salary` desc limit 1,1

Answer / nitin munjani

Using Row_Number() function

Here N refers the Nth highest salary

select empid,empname from mstemployee where empsalary in (
(select empsalary from
(select Row_Number() over(order by empsalary desc)
rownm,empsalary from mstemployee
group by empsalary) o where rownm=N))

Answer / vinay upadhyay

These are tested queries to find Nth highest salary

Select distinct(sal) From employee emp Where n =
( Select Count(Distinct y.sal) From employee y Where y.sal
>=emp.sal)

OR

select distinct(salary) from employee order by salary desc
limit n-1,1

Answer / ravindra babu

select sal from (select sal from(Select distinct sal from
EMP order by sal desc)Where rownum <=2 order by sal asc)
Where rownum=1;

Answer / sudhir

DISPLAY THE RECORDS WHO'S GETTING 3RD HIGHEST SALARY.

SELECT * FROM EMP E1
WHERE 3 =(SELECT COUNT(DISTINCT(SAL))
FROM EMP E2
WHERE E1.SAL <= E2.SAL)

Does server sql treat char as a variable-length or fixed-length column?

0 Answers  

How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode?

3 Answers   HCL,

does physical index sort data

1 Answers  

Where do you find the default Index fill factor and how to change it?

0 Answers  

How to stop a loop early with break statements in ms sql server?

0 Answers  

How to transfer an existing table from one schema to another schema in ms sql server?

0 Answers  

What is filter index?

0 Answers  

How would we use distinct statement? What is its use?

0 Answers  

What is the meaning of sql server?

0 Answers  

What is the datatype of rowid?

0 Answers  

Can a stored procedure call itself(recursive). If yes what level and can it be controlled.?

2 Answers  

How to connect sql server management studio express to sql server 2005 express?

0 Answers