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)

what are the basic functions for master, msdb, model, tempdb and resource system databases? : sql server database administration

0 Answers  

How to override dml statements with triggers?

0 Answers  

What is the difference between having and where clause?

4 Answers  

i need to know how i display department which has salary > =5000 for the below table Department ----------- salary deptname 1000 a 3000 a 2000 b 3000 b 4000 c 5000 c kindly send the query to thilakvinoth13@gmail.com

6 Answers  

How will you hide an attribute? : sql server analysis services, ssas

0 Answers  

What is query processing?

0 Answers  

What is difference between index and primary key?

0 Answers  

how to count datewise data in sqlserver

4 Answers   CarrizalSoft Technologies, IndusInd Bank,

What is transaction server consistency?

0 Answers  

what is index seek and index scan?

2 Answers  

What is log shipping? Can we do logshipping with SQL Server 7.0 - Logshipping is a new feature of SQL Server 2000. We should have two SQL Server - Enterprise Editions. From Enterprise Manager we can configure the logshipping. In logshipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db and we can use this as the DR (disaster recovery) plan.

0 Answers  

What is the difference between DATETIME2 and DATETIME?

0 Answers   Infosys,