Home / mySQL / Get highest or second highest or lowest values from table – MYSQL

Get highest or second highest or lowest values from table – MYSQL

Hi Guys,
Today I share some useful topic related to MYSQL. The MYSQL is very helpful in our project or queries for different functionality.
The MYSQL is most popular Open source management system.

Suppose that, you are given the following simple database table called EmployeeDetails table that has two field like EmployeeID and Salary. We can fetch the data from database and also fetch content from database table based on condition.

It is easy to select the highest or lowest record in the database table with the MAX or MIN function.

Fetch Full data from database table as EmployeeDetails:

select * from EmployeeDetails;

EmployeeID Salary
1 4000
2 8000
3 8500
4 14000

Get the highest salary from the table EmployeeDetails:

Select MAX(Salary) from EmployeeDetails;

Running the SQL above would return us 14000, which is of course the highest salary in the EmployeeDetails table.

Get the second highest salary from the table EmployeeDetails

SELECT MAX(Salary) FROM EmployeeDetails WHERE Salary NOT IN (SELECT MAX(Salary) FROM EmployeeDetails);

Running the SQL above would return us 8500, which is of course the 2nd highest salary in the EmployeeDetails table.
Code Explanation:The SQL above, first find the highest salary values from the EmployeeDetails table using “Select MAX(Ssalary) from EmployeeDetails”. Then, adding the “WHERE Salary NOT IN” in front basically creates a new set of Salary values that does not include the highest Salary value. For instance, if the highest Salary in the EmployeeDetails table is 14000 then that value will be excluded from the results using the “NOT IN” operator, and all values except for 14000 will be retained in the results.

Here is another SQL query to find second highest salary using subquery and < operator instead of IN clause:

SELECT max(salary) FROM EmployeeDetails WHERE salary < (SELECT max(salary) FROM EmployeeDetails);

Running the SQL above would return us 8500, which is of course the 2nd highest salary in the EmployeeDetails table.

Get the lowest salary from the table EmployeeDetails:

Select MIN(Salary) from EmployeeDetails;

Running the SQL above would return us 4000, which is of course the 2nd highest salary in the EmployeeDetails table.

Loading Facebook Comments ...
Social Media Auto Publish Powered By : XYZScripts.com