query to find the second highest salary SQL

If you’re working with freelance companies or contract workers, it is likely that you will need an efficient way for your client to enter the data for employees’ salaries. I recommend using a MySQL database to store this information. In this article, I’ll go over how to use MySQL commands in creating a table and entering data in the best possible way. We’ll be using the “interval” type of values here, but you can read up on string, date and datetime types when you are finished with this article. In this article we’ll also discuss select 3rd highest salary in sql, second highest salary in sql without subquery.

What are the top considerations to keep in mind when determining what your salary should be? There are many questions that you will have to think about. One question that people often ask me is, “How can I know what my competitors in the same industry or job level are making?” This post will help you understand why you should know the answer to this question.

query to find the second highest salary SQL

When it comes to a salary negotiation, many job seekers are confused on how to even get a raise. One of the most important factors in salary negotiations is knowing what other companies are paying for the same position. But how can you know what your competitors are making?

Write a SQL query to get the second highest salary from the Employee table.

+—-+——–+
| Id | Salary |
+—-+——–+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+—-+——–+

For example, given the above Employee table, the second highest salary is 200. If there is no second highest salary, then the query should return NULL. You can write SQL query in any of your favorite databases e.g. MySQL, Microsoft SQL Server, or Oracle. You can also use database specific feature e.g. TOP, LIMIT or ROW_NUMBER to write SQL query, but you must also provide a generic solution which should work on all database. 

In fact, there are several ways to find the second highest salary and you must know a couple of them e.g. in MySQL without using the LIMIT keyword, in SQL Server without using TOP, and in Oracle without using RANK and ROWNUM. 

Once you solve the problem, the Interviewer will most likely increase the difficulty level by either moving to the Nth salary direction or taking away these built-in utilities.

Second Highest Salary in MySQL without LIMIT

Here is a generic SQL query to find the second highest salary, which will also work fine in MySQL. This solution uses a subquery to first exclude the maximum salary from the data set and then again finds the maximum salary, which is effectively the second maximum salary from the Employee table.

SELECT MAX(salary) FROM Employee 
WHERE Salary NOT IN ( SELECT Max(Salary) FROM Employee);

This will return 200 in our case.

Here is another solution that uses sub query but instead of IN clause it uses < operator

SELECT MAX(Salary) From Employee
 WHERE Salary < ( SELECT Max(Salary) FROM Employee);

You can use this SQL query if the Interviewer ask you to get second highest salary in MySQL without using LIMIT.  You can also use distinct keyword if your Employee table may contain duplicate salary, In this example there is no such record, so I have not used distinct.

Second Highest Salary using Correlated SubQuery

Previous SQL query was also using subquery but it was non-correlated, this solution will use a correlated subquery. This is also a generic solution to find Nth highest salary in the Employee table. For each record processed by outer query, inner query will be executed and will return how many records has records has salary less than the current salary. If you are looking for second highest salary then your query will stop as soon as inner query will return 2.

SELECT Id, Salary
FROM Employee e
WHERE 2=(SELECT COUNT(DISTINCT Salary) FROM Employee p
WHERE e.Salary<=p.Salary)

Second Maximum Salary in MySQL using LIMIT

MySQL has a special keyword called LIMIT which can be used to limit the result set e.g. it will allow you to see the first few rows, last few rows, or range of rows. You can use this keyword to find the second, third or Nth highest salary. Just use order by clause to sort the result set then print the second salary as shown below :


SELECT Salary FROM 
  (SELECT Salary FROM Employee ORDER BY salary DESC LIMIT 2) AS Emp 
ORDER BY salary LIMIT 1;

In this solution, we have first sorted all salaries from the Employee table in decreasing order, so that the 2 highest salaries come at top of the result set. After that, we took just two records by using LIMIT 2. Again we did the same thing but this time we sort the result set in ascending order so that the second-highest salary comes at the top. Now we print that salary by using LIMIT 1. Simple and easy, right?

Second Highest Salary using SQL Server Top Keyword

Just like MySQL has LIMIT keyword, which is immensely helpful in sorting and paging, Microsoft SQL Server also has a special keyword called TOP, which as name suggest prints top records from result set. You can print top 10 records by saying TOP 10. I frequently use this keyword to see the data from a large table, just to understand columns and data inside it. Here is the SQL query to find second maximum salary in SQL Server :

SELECT TOP 1 Salary FROM 
( SELECT TOP 2 Salary FROM Employee ORDER BY Salary DESC) AS MyTable 
ORDER BY Salary ASC;

Here is the output of the above query running on Microsoft SQL Server 2014 :

Second Highest Salary in MySQL and MSSQL

Now It’s time to apply the knowledge you have learned so far. Solve following SQL queries at your convenience :

  1. Write SQL query to get a third highest salary from Employee table?
  2. How do you find the 4th highest salary in MySQL without using the LIMIT keyword?
  3. Write SQL query to find second highest salary in Oracle database using ROWNUM?
  4. How to find Nth highest salary in SQL Server without using TOP keyword?
  5. Find the second highest salary in Oracle using rank?
  6. How to find the top 3 salaries in Oracle without using ROW_NUMBER or RANK()?

That’s all about different ways to find the Second highest Salary in MySQL and SQL Server.  We have seen examples to get the second highest salary in MySQL by using LIMIT and without using LIMIT. Similarly in MSSQL, we know how to get the second highest salary by using TOP and without using the TOP keyword. 

We can see that the highest salary is $31,000.00 and the lowest salary is $11,500.00. This means that the mean of this data set is ($31,000+$11,500+$11,500+$10800.00)/4 = $17600.00. However, because there are only four values in our data set, the median is ($31,000+$11,500+$11,500)/2 = $15750.00. Therefore the arithmetic mean (i.e., the average salary) of these salaries is $17600/2 = $8700.00

second highest salary in sql without subquery

Here is a generic SQL query to find the second highest salary, which will also work fine in MySQL. This solution uses a subquery to first exclude the maximum salary from the data set and then again finds the maximum salary, which is effectively the second maximum salary from the Employee table.

SELECT MAX(salary) FROM Employee 
WHERE Salary NOT IN ( SELECT Max(Salary) FROM Employee);

This will return 200 in our case.

Here is another solution that uses sub query but instead of IN clause it uses < operator

SELECT MAX(Salary) From Employee
 WHERE Salary < ( SELECT Max(Salary) FROM Employee);

You can use this SQL query if the Interviewer ask you to get second highest salary in MySQL without using LIMIT.  You can also use distinct keyword if your Employee table may contain duplicate salary, In this example there is no such record, so I have not used distinct.

select 3rd highest salary in sql

Finding Nth highest salary in a table is the most common question asked in interviews. Here is a way to do this task using dense_rank() function. 

Consider the following table: 

Employee 

ename sal 
A23000
B31000
C24500
D35000
E28500
F31500
G39800
H51000
I39800

Query : 

select * from(
select ename, sal, dense_rank() 
over(order by sal desc)r from Employee) 
where r=&n;
To find to the 2nd highest sal set n = 2
To find 3rd highest sal set n = 3 and so on.

Output: 

DENSE_RANK: 

  1. DENSE_RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER. The ranks are consecutive integers beginning with 1.
  2. This function accepts arguments as any numeric data type and returns NUMBER.
  3. As an analytic function, DENSE_RANK computes the rank of each row returned from a query with respect to the other rows, based on the values of the value_exprs in the order_by_clause.
  4. In the above query the rank is returned based on sal of the employee table. In case of tie, it assigns equal rank to all the rows. 
     

Alternate Solution:
—————————————————————————————————————————————————————————————————————– 

CREATE TABLE `Employee` ( 
`ENAME` varchar(225) COLLATE utf8_unicode_ci NOT NULL, 
`SAL` bigint(20) unsigned NOT NULL, 
PRIMARY KEY (`ENAME`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 

————————————————————————————————————————————————————- 

6th highest
mysql> select * from ((select * from Employee 
       ORDER BY `sal` DESC limit 6 ) AS T) 
       ORDER BY T.`sal` ASC limit 1;
Alternate use of Limit:
select * from Employee ORDER BY `sal` DESC limit 5,1; // will return 6th highest 


+-------+-----+
| ENAME | SAL |
+-------+-----+
| B     | 300 |
+-------+-----+
1 row in set (0.00 sec) 

——————————————————————————————————————————————————– 

mysql> select * from Employee;
+-------+-----+
| ENAME | SAL |
+-------+-----+
| A     | 100 |
| B     | 300 |
| C     | 200 |
| D     | 500 |
| F     | 400 |
| G     | 600 |
| H     | 700 |
| I     | 800 |
+-------+-----+
8 rows in set (0.00 sec) 

Thanks to Vijay for suggesting this alternate solution. 

Alternate Solution:

Suppose the task is to find the employee with the Nth highest salary from the above table. We can do this as follows: 

  1. Find the employees with top N distinct salaries.
  2. Find the lowest salary among the salaries fetched by the above query, this will give us the Nth highest salary.
  3. Find the details of the employee whose salary is the lowest salary fetched by the above query.

Query:  

SELECT * FROM Employee WHERE sal = 
         (
            SELECT MIN(sal) FROM Employee 
            WHERE  sal IN (
                                 SELECT DISTINCT TOP N
                                     sal FROM Employee 
                                         ORDER BY sal DESC
                             )
        )

The above query will fetch the details of the employee with the Nth highest salary. Let us see how: 

  • Consider N = 4.
  • Starting with the most inner query, the query: “SELECT DISTINCT TOP sal FROM Employee ORDER BY sal DESC” will produce the below result:  
51000
39800
35000
31500
  • The next outer query is: “SELECT MIN(sal) FROM Employee WHERE sal IN ( Result_Set_of_Previous_Query )“. This will return the below result:
31500
  • You can see that the above returned result is the required 4th highest salary.
  • Next is the most outer query, which is: “SELECT * FROM Employee WHERE sal = Result_of_Previous_Query“. This query will return the details of employees with 4th highest salary. 
________________________
ename             sal
________________________
  F        |     31500
           |
________________________

Another Solution:

Here N = nth Highest Salary eg. 3rd Highest salary : N=3 .

SELECT ename,sal from Employee e1 where 
        N-1 = (SELECT COUNT(DISTINCT sal)from Employee e2 where e2.sal > e1.sal) 

Solution using Limit : 

    Select Salary from table_name order by Salary DESC limit n-1,1;

        Here we are ordering our salary in descending order so we will get highest salary first and then subsequently lower salaries. 

        Limit clause has two components, First component is to skip  number of rows from top and second component is display number of rows we want. 

   let us see with an example :

    To find 4th Highest salary query will be :

 Select Salary from table_name order by Salary DESC limit 3,1;

   Here  we are skipping 3 rows from Top and returning only 1 row after skipping .

 You can also find names of employees having Nth Highest Salary 

Select Emp_name from table_name where Salary =( Select Salary from table_name order by Salary DESC limit n-1,1);

There can be another question like find Nth Lowest Salary . In order to that , just reverse order using ASC ( if you don’t specify by default column will be ordered in ascending order).

 Select Salary from table_name order by Salary limit n-1,1;

Leave a Reply