Find the second highest value in a MySQL Table

This question often asked in Interviews in many ways, such as

1. how to find the second highest salary in MySQL
2. how to find the second highest marks in MySQL

and more..

Here, I will tell you step by step to do it, so you can try yourself:

Lets create a table first (Run this below query in your SQL browser to create a table and insert dummy values):

--
-- Table structure for table `employee`
--

CREATE TABLE IF NOT EXISTS `employee` (
  `emp_id` int(3) NOT NULL AUTO_INCREMENT,
  `emp_name` varchar(100) NOT NULL,
  `emp_salary` int(10) NOT NULL,
  PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `employee`
--

INSERT INTO `employee` (`emp_id`, `emp_name`, `emp_salary`) VALUES
(1, 'Roj', 100),
(2, 'Fren', 50),
(3, 'Rin', 300),
(4, 'Yen', 150),
(5, 'Sippu', 500),
(6, 'Noop', 250);

The above code will create a table like below:

So, basically the second highest values in the above table is 300

There are many ways you can write a query to find out the second highest value, one of them is this:

SELECT MAX( emp_salary ) 
FROM employee
WHERE emp_salary NOT 
IN (
SELECT MAX( emp_salary ) 
FROM employee
)

The above query will give you the second highest salary from the employee table. Basically doing a sub query to find out the actual MAX value, then again checking with NOT IN function to eliminate the actual MAX value and to show the second highest value.