SQL query to find second highest salary?

This is what happened to me as you hit this page, hey - this article helps you.

I'm sure ever a developer might not have come across this challenge, either in application development or in interview question. 

 "Finding second or third highest or lowest salary in a employee table?" 

As you know, Microsoft MSSQL Server , and MySQL which is open source, are the two main databases we frequently work with -isn't?

How Microsoft SQL Server handles in SQL Query?

In SQL Server using Common Table Expression or CTE, we can find the max value, nth highest value,2nd highest value,using joins, using rank() function,using subquery, second highest, third highest or largest salary from table, all these all possibilities semantic key terms to find results. 

Let's consider this Employee table for an example 

Name	Salary
David Brown	$25,000
Robert Melvin	$23,500
Peter Paul	$24,000

Which employee has highest salary in a table?

Obviously you know David Brown has the highest salary $25,000 in the first row table.

Now, let's find largest value or salary from above table using keyword MAX; 

 

SELECT Name, MAX(Salary) AS Salary FROM  Employee; 

So, are there any tips other than MAX keyword to find highest value or salary?

The answer is yes, using function ROW_NUMBER() apply row number on the column 'Salary' order by descending; the inner or subquery returns records in descending order. Now we apply order sequence number to 1 which finally returns the max salary.

1. Find highest value or salary using ROW_NUMBER()

SELECT Name, Salary

FROM (

• SELECT Name, Salary, ROW_NUMBER() OVER(ORDER BY Salary Desc) AS Salary_Order
• FROM   Employee

) results

WHERE results. Salary_Order = 1;  

Now, let's find second highest or largest salary in a table. The answer is the last row Peter Paul and with salary $24,000

2. Find second highest value or salary using ROW_NUMBER()

SELECT Name, Salary

FROM (

• SELECT Name, Salary, ROW_NUMBER() OVER(ORDER BY Salary Desc) AS Salary_Order
• FROM   Employee

) results

WHERE results. Salary_Order = 2;  

Now, let's find third highest or largest salary in a table. The answer is the last row Robert Melvin and with salary $23,500

3. Find third highest value or salary using ROW_NUMBER()

SELECT Name, Salary

FROM (

• SELECT Name, Salary, ROW_NUMBER() OVER(ORDER BY Salary Desc) AS Salary_Order
• FROM   Employee

) results

WHERE results. Salary_Order = 3;  

And, now let's look at using Sub Query to get second largest salary

 

 Find second highest value or salary using Sub Query

SELECT Name, MAX(Salary) AS Salary

  FROM Employee

 WHERE Salary < (SELECT MAX(Salary)

              FROM Employee); 

In SQL Server using Common Table Expression (CTE), we can find the second highest salary

 

 Find second highest value or salary using Common Table Expression

WITH X AS

(

    SELECT *

     DENSE_RANK() OVER (ORDER BY Salary Desc) AS Rank

     FROM Employees

)

SELECT Name

FROM X

WHERE Rank=2; 

 

How MySQL Server handles in SQL Query?

As we have seen SQL Server follows tactics to find 2nd highest and here almost similar keywords and terms used yet there is special keyword called LIMIT  clause.

Sometimes back - When I had to generate sequence number 1,2,3,4, in query results to display the list of employees in website dashboard- This is how I solved

What does this LIMIT clause say?

The LIMIT clause is used in the SELECT statement to constrain the number of rows to return. The LIMIT clause accepts one or two arguments. The values of both arguments must be zero or positive integers.

And most of the time working with MySQL reference gone missing and caused whole lot time to complete it - [Solved] Could not not load assembly .

And, now let's look at using Sub Query and LIMIT to get second largest value or salary

 

 1. Find second highest value or salary using NOT IN

SELECT Name, MAX(Salary) AS Salary

  FROM Employee

 WHERE Salary NOT IN (SELECT MAX(Salary)

              FROM Employee); 

 

 2. Find second highest value or salary using <

  FROM Employee

 WHERE Salary < (SELECT MAX(Salary)

              FROM Employee); 

 

 3. Find second highest value or salary using LIMIT 

SELECT Name,  Salary

FROM (

                  SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 2

       ) AS Emp ORDER BY Salary LIMIT 1;         

 

 Summary

 So far you have gone through how Microsoft SQL Server and MySQL databases use query to find nth highest value in a table, along with different types of methods to get results.