Getting second highest Salary value in MySQL

second highest salary in oracle
how to find second highest salary in sql using subquery
how to find second highest salary in sql using joins
nth highest salary in sql
nth highest salary in db2
2nd highest salary in sql stack overflow
how to find second highest salary in sql using rank() function
select name of employee with max(salary)

In a given question,

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 query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

I wrote a code for this question, however, the expected output is different.

My Code:

SELECT
CASE
    WHEN COUNT(*) = 1 THEN NULL
    ELSE (SELECT Salary FROM Employee HAVING Salary < MAX(Salary) ORDER BY Salary DESC LIMIT 1)
END AS SecondHighestSalary
FROM Employee;

I think there's something wrong with my code but I cannot find what is the actual problem here. My code returns 100 not 200. What have I gotten wrong?

+---------------------+
| SecondHighestSalary |
+---------------------+
| 100                 |
+---------------------+

You can utilize LIMIT {[offset,] row_count}. Refer https://dev.mysql.com/doc/refman/8.0/en/select.html

Order by Salary in descending order, and get the second row by defining OFFSET as 1. We will use DISTINCT on Salary as there is a possibility to have multiple rows for the highest salary.

SELECT DISTINCT
  Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1,1

Getting second highest Salary value in MySQL, Here's one that accounts for ties. Name Salary Jim 6 Foo 5 Bar 5 Steve 4 SELECT name, salary FROM employees WHERE salary = (SELECT  MySQL query to find the second highest salary. For example, in above table, “Alen” has the second maximum salary as 8000.

You can try below

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

Get the second highest value in a MySQL table, Note that instead of nesting for second, third, etc largest salary, we can find nth salary using general query like in MySQL: SELECT salary FROM employee  How to find the employee whose salary is second highest. For example, in above table, “ghi” has the second highest salary as 500000. Below is simple query to find the employee whose salary is highest. SELECT name, MAX (salary) as salary FROM employee.

Try that out:

SELECT salary FROM Employee ORDER BY salary DESC LIMIT 1,1

or to a deeper approach you can use something like:

SELECT salary FROM Employee GROUP BY salary ORDER BY salary DESC LIMIT 1,1

All that queries have high performance, since they does not have any subqueries.

SQL query to find second highest salary?, Read LeetCode's official solution for Second Highest Salary. Write a SQL query to get the second highest salary from the Employee table. MySQL. SELECT IFNULL( (SELECT DISTINCT Salary FROM Employee ORDER  There are many ways you can write a query to find out the second highest value, one of them is this: MySQL SELECT MAX( emp_salary ) FROM employee WHERE emp_salary NOT IN ( SELECT MAX( emp_salary ) FROM employee )

If you want to display a empty_row(null) in case there is no highest salary then the following does it, if there is a value then it gets shown

select (select salary
          from Employee ORDER BY salary DESC LIMIT 1,1
        ) as x

Second Highest Salary, In this article you will learn how to write MySQL query to find the second highest salary from the employees table using sub query and IN  Second Highest Salary in MySQL without LIMIT Here is a generic SQL query to find second highest salary, which will also work fine in MySQL. This solution uses subquery to first exclude the maximum salary from the data set and then again finds maximum salary, which is effectively the second maximum salary from the Employee table.

Here is the solution.

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

MySQL query to get second highest salary, Sample table and data for Nth Highest Salary Problem INTO Employee VALUES ('Rick', 3000); INSERT INTO Employee VALUES ('John', ways to solve this problem of finding the Nth maximum salary from Employee table  Questions: I have a table of employees and salaries defined that way: "name" (type: VARCHAR) "salary" (type: INTEGER) What query can I use to get the second highest salary in this table? Answers: Here’s one that accounts for ties. Name Salary Jim 6 Foo 5 Bar 5 Steve 4 SELECT name, salary FROM employees WHERE

4 Ways to find Nth highest salary in SQL, How do you find second maximum salary of employee in SQL is a In this section, we will write SQL queries to get the second highest salary of Employees. mysql> SELECT max(salary) FROM Employee WHERE salary NOT IN Now second highest salary of employee is at top so just take that value. MySQL MySQLi Database To find the nth highest value of a column, you need to use ORDER BY DESC with LIMIT clause. If you want the second highest value of a column, use the below syntax: SELECT *FROM yourTableName ORDER BY DESC yourColumnName LIMIT 1,1;

How to find second highest or maximum salary of , If we remove the highest salary from a group of salary values, then we will have a new group of values whose highest salary is actually the 2nd highest in the original Didn't get the answer. Contact people of Talent-Mysql directly from here. The following MySQL statement finds the highest and lowest salary From An employees Table: SELECT MAX (salary), MIN (salary) FROM employees; MAX (salary) MIN (salary) 12000. 3000. In above MySQl query we used-. The MySQL MAX function returns the maximum value in a set of values.

How To Find Second Highest Salary in MySQL, If we remove the highest salary from a group of salary values, then we will have a new group of values whose highest salary is actually the 2nd highest in the original Employee table. So, we need to first find out the highst salary, using Max() function, and have to put into "NOT IN" to exclude it.

Comments
  • You can get nth highest record visit stackoverflow.com/a/26732513/2893413
  • @Sadikhasan how does it even matter, as OP only wants the salary figure, not the full row.
  • Thank you for your answer. But I tried this and this does not return null when there's only one employee. I think maybe adding IFNULL() would do?
  • @Poream3387 hmm.. let me test. I havent tested it.
  • @Poream3387 it returns no results: db-fiddle.com/f/e1Efjj6SY5xVrTEuJ9oP4j/0 - It is pretty much equivalent to getting null. You can handle this in application code (eg: PHP), when no rows are returned.
  • By wrapping the answer in a select you would be able to get "null" value if there is no second highest salary
  • The OP wanted to return a row with "null" value in case there is no second highest salary.. Without the "wrapper, the query would return "no_rows_returned" or an empty record set.
  • Right, got it!!