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 index slows down insert statements?

0 Answers  

can i write function in stored procedure and stored procedure in function and nested procedure.Give one example for each question?

1 Answers   Cap Gemini, TCS,

Assume,there are three tables in a database, Can i have both primary key and foreign key for each table in the same database?

4 Answers  

Difference between group by clause and having clause in SQL?

0 Answers   Flextronics,

What is side by side migration in sql server?

0 Answers  

Can a database be shrunk with users active?

0 Answers  

What is deploy, process and build? : sql server analysis services, ssas

0 Answers  

Explain the basic concepts of SQL server architecture?

2 Answers   College School Exams Tests,

How to delete existing rows in a table?

0 Answers  

What do mean by xml datatype?

0 Answers  

What do you need to connect php to sql server?

0 Answers  

What is field in algebra?

0 Answers