Find Nth Highest Salary in SQL Server

Find Nth Highest Salary

In this article I am explaining various ways to get the Nth highest salary from an EMPLOYEE table.

1. Create a Table
   
   Here the EMPLOYEE table has the three coulmns Id, Name and SALARY. Id is a primary key which is an autogenerated column and incremented by 1 from the seed value 1. SALARY is a field where we store the salary of an employee. The following figure shows the EMPLOYEE table creation code.
   
   
    
2. Insert Employee data in EMPLOYEE table
   
   Now we insert a number of rows in the EMPLOYEE table using the row constructor. In this we define each row in brackets ( ) and each row is seprated by a comma. In the brackets each filed is also seprated by a comma. The following figure shows that 10 rows were inserted into the EMPLOYEE table using a single line of code but in this code we are not passing the Id field because it is an autogenerated column.
   
   
    
3. Show all employee data
   
   After inserting 10 rows into the EMPLOYEE table we use a select statement to show all employee data in the EMPLOYEE table.
   
   
    
4. Get 3rd Highest Salary using TOP
   
   To get the 3rd highest salary from the employee table we create a result set of 3 rows and after that we get the top 1 from these 3 rows.
   First of all create a SELECT statement which returns 3 rows. In this we get the top 3 rows using an order by salary in descending order from the EMPLOYEE table; see:
   
   
   
   Now we have the above result which has 3 rows and we want the lowest salary from this. So to get the lowest salary we need to specify the ascending order for SALARY so we can get the lowest salary on top.  So we give an alias name to the result set "Emp".
   
   
   
   Now we can get the lowest salary on top. In other words we have the three highest salaries of the EMPLOYEE table data on top like:
   
   
   
   Now we can get the topmost salary from this like:
   
   
   
   We get the result as in the following figure:
   
   
   
   Suppose we don't want to specify ascending order for salary obtained from the EMPLOYEE table; in that case we can't define the TOP 1 salary so we will use a MIN to get the lowest salary from this result set which is the third highest salary in the EMPLOYEE table.
   
   
   And in the output we will get the thirrd highest salary from the EMPLOYEE table.
   
   
    
5. Get 4th Highest salary using MAX
   
   To get nth highest salary using MAX we need to define a SELECT statement which return the n highest salaries from the EMPLOYEE table.
   
   
   
   It will return 3 highest salaries in the Employee Table.
   
   Image13
   
   Now we have the 3 highest salaries in the EMPLOYEE table. So now we get the maximum salary from the EMPLOYEE table which is not in the 3 highest salaries; that salary will be 4th highest salary in the EMPLOYEE table. This code is as in the following figure.
   
   
   
   Now we have 4th highest salary.
   
   
    
6. Get 4th Highest Salary without TOP and MAX
   
   Here we create two aliases of a single table and compare the first table's salaries to another table's salaries row by row.
   
   
   
   In this we compare the first table, each salary to the second table's each salary. We want to get the 4th highest salary so that means there will be 3 salaries greater than the 4th salary. So now we count how many salaries are greater than the second table by comparing to the first table each salary where we will get that there are 3 salaries greater than from a specific salary from the first table that the salary will be the 4th highest salary.