Most commonly asked interview question for a SQL developer that how to find second highest salary in MySQL, there are many other ways to perform an output to show second highest salary but i mostly prefer very easy and short form of writing SQL query. so to make it easy i have already written a query.
Data without using LIMIT and OFFSET
SELECT EMP_CODE, EMP_SALARY FROM EMP ORDER BY EMP_SALARY DESE
Sample and short query to find second highest salary in MySQL using LIMIT and OFFSET.
SELECT EMP_CODE, EMP_SALARY FROM EMP ORDER BY EMP_SALARY DESC LIMIT 1 OFFSET 1 -- OR YOU CAN EVEN WRITE -- LIMIT 1, 1
So let me explain how to find second highest salary in MySQL in detail.
1, First select what type of columns you want to show in select statement where i preferred EMP_CODE, EMP_SALARY
2, You need to ORDER BY EMP_SALARY in DESC so that the salary values appears from highest values to the smallest.
3, Just pass LIMIT 1 OFFSET 1 or you can even write LIMIT 1,1 in short form
Lets understand how OFFSET works to retrieve the data of your preferred Nth salary.
As we know LIMIT 1 returns only one row of your select query, but when we use OFFSET 1, then the LIMIT 1st row get skipped and the second row becomes your first row. For example
If you wanted to get the 3rd Highest Salary
then the query would be something like
SELECT EMP_CODE, EMP_SALARY FROM EMP ORDER BY EMP_SALARY DESC LIMIT 2 OFFSET 1
In the above query i mentioned LIMIT 2 OFFSET 1, this means it skips the first 2 rows and third row becomes your first row with LIMIT 2 OFFSET 1
The same way if you wanted to get the 4th Highest Salary
SELECT EMP_CODE, EMP_SALARY FROM EMP ORDER BY EMP_SALARY DESC LIMIT 3 OFFSET 1
In the above query i mentioned LIMIT 3 OFFSET 1, this means it skips the first 3 rows and fourth row becomes your first row with LIMIT 3 OFFSET 1