аппарат теплообменный пластинчатый разборный | насос циркуляционный для отопления | насосы для отопления | насосы циркуляционные для отопления | насосы челябинск | пластинчатые теплообменники цена | пластинчатый теплообменник цена | теплообменник alfa laval | теплообменник пластинчатый разборный | теплообменное оборудование | модульные котельные | проект блочно модульной котельной

How To Find Second,Third,Fourth Highest Salary in MySQL

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

OUTPUT

EMP_CODE EMP_SALARY
200107 4500.00
200134 4200.00
200108 4000.00
200119 4000.00
200113 4000.00
200128 3500.00
200114 3500.00
200109 3500.00
200101 3000.00
200110 3000.00

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

OUTPUT

EMP_CODE EMP_SALARY
200134 4200.00

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

OUTPUT

EMP_CODE EMP_SALARY
200108 4000.00

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

OUTPUT

EMP_CODE EMP_SALARY
200119 4000.00

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

intactabode