To Find Highest Salary, nth Highest Salary, Lowest Salary, nth Lowest Salary

To Find Highest Salary, nth Highest Salary, Lowest Salary, nth Lowest Salary

Correlated sub queries are more generalized to find n'th highest or nth lowest salary.

To Find Maximum Salary

SELECT * FROM `student` WHERE mark=(select max(mark) from student)

n means 1 st, 2nd, 3rd highest or lowest salary dont give n values,Give n-1 values
For eg
please give

(if you want 2nd highest salary give n=1
for 3rd n=2 like that.)

To find nth highest salary

SELECT * FROM Employee E1 WHERE n-1 =
(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary < E2.Salary)

To find 2nd highest salary

SELECT * FROM Employee E1 WHERE 1 =
(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary < E2.Salary)

select top 2 * from Trainee where Salary in(select top 1 Salary from Depart)order by Salary DESC

To find 2nd lowest salary

SELECT * FROM Employee E1 WHERE 1 =
(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary > E2.Salary)

To find nth lowest salary

SELECT * FROM Employee E1 WHERE n-1 =
(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary > E2.Salary)