How to find nth highest salary in sql
0 likes589 views
The document describes three ways to find the nth highest salary from employee data: 1) Using a subquery to find salaries lower than the maximum; 2) Using a CTE with dense ranking to filter the nth ranked salary; 3) Using row numbering, which only works if there are no duplicate salaries. Examples of SQL queries are provided for each method.
How to find nth highest salary in sql
- 1. How to Find nth Highest Salary Points covered with this presentation How to find nth highest salary using Sub-query How to find nth highest salary using CTE How to find the 2nd,3rd or 15th highest salary Samir Bhogayta | 8401903277 | www.samirbhogayta.in
- 2. ID FirstName LastName Gender Salary 1 Ramesh Sharma Male 70000 2 Rakesh Srivastav Male 60000 3 Nirav Gohel Male 45000 4 Suresh Rathod Male 70000 5 Samir Bhogayta Male 45000 6 Sujata Nigam Female 30000 7 Nirali Patel Female 35000 8 Kusum Patel Female 80000 Find the highest salary is very straight forward. Simply use the Max() function as shown below. Select max(Salary) from Employees To find the second highest salary use a sub query along with Max() function as shown below. Select max(Salary) from Employee where Salary < (Select max(Salary) from Employees) Samir Bhogayta | 8401903277 | www.samirbhogayta.in
- 3. ID FirstName LastName Gender Salary 1 Ramesh Sharma Male 70000 2 Rakesh Srivastav Male 60000 3 Nirav Gohel Male 45000 4 Suresh Rathod Male 70000 5 Samir Bhogayta Male 45000 6 Sujata Nigam Female 30000 7 Nirali Patel Female 35000 8 Kusum Patel Female 80000 To find nth highest salary using Sub-query. Select top 1 Salary from (Select distinct top N Salary from Employees order by Salary desc) Result Order by Salary To find nth highest salary using CTE. With Result as (Select Salary, DENSE_RANK() over (order by Salary desc) as DENSERANK from Employees) Select top 1 Salary from Result where DENSERANK = N Samir Bhogayta | 8401903277 | www.samirbhogayta.in
- 4. ID FirstName LastName Gender Salary 1 Ramesh Sharma Male 70000 2 Rakesh Srivastav Male 60000 3 Nirav Gohel Male 45000 4 Suresh Rathod Male 70000 5 Samir Bhogayta Male 45000 6 Sujata Nigam Female 30000 7 Nirali Patel Female 35000 8 Kusum Patel Female 80000 Please note : On many websites, you have seen that, the following query can be used to get the nth highest salary. The below query will only work if there are no duplicates. With Result as (Select Salary, ROW_NUMBER() over (order by Salary desc) as ROWNUMBER from Employees) Select Salary from Result where ROWNUMBER = N Samir Bhogayta | 8401903277 | www.samirbhogayta.in