Find nth highest salary from Employee table
Query to retrieve two minimum and maximum salaries from the Employee table.
To retrieve two minimum salaries, you can write a query as below:
SELECT DISTINCT Salary FROM Employee e1 WHERE 2 >= (SELECT COUNT(DISTINCT Salary) FROM Employee e2 WHERE e1.Salary >= e2.Salary) ORDER BY e1.Salary DESC;|
1
2
3
|
SELECT DISTINCT Salary FROM Employee E1 WHERE 2 >= (SELECTCOUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary <= E2.Salary) ORDER BY E1.Salary DESC; |
Query to find the Nth highest salary from the table without using TOP/limit keyword.
|
1
2
3
4
5
6
|
SELECT Salary FROM Employee E1 WHERE N-1 = ( SELECT COUNT( DISTINCT ( E2.Salary ) ) FROM Employee E2 WHERE E2.Salary > E1.Salary ); |
Query to find the third-highest salary from the EmpPosition table.
|
1
2
3
4
5
6
|
SELECT TOP 1 salaryFROM(SELECT TOP 3 salaryFROM employee_tableORDER BY salary DESC) AS empORDER BY salary ASC; |
Tags:
#nth salary
# database
#
