Finding the Second Highest Salary:

Consider the following table. It consists of a set of employee records and we are going to use in our queries to get salary that is second largest.

Table Name: Employees

ID	Name	Designation	Salary
1	Colleen Hurst	Regional Director	205500
2	Garrett Winters	Accountant	170750
3	Quinn Flynn	Support Lead	342000
4	Jena Gaines	Software Engineer	133600
5	Brielle William	Regional Director	372000

1. Using Subquery:

Here is the simple query to find the highest salary from the above 'Employees' table. It uses the max() aggregate function to return the maximum value of a field (or expression).

SELECT MAX(Salary) FROM Employees

// output: 372000

Simply nesting this query will give you the second highest salary. It excludes the maximum salary from the table using subquery and fetches the maximum salary again.

SELECT MAX(Salary) FROM Employees WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employees)

// output: 342000

Below is the slightly different version of this sub query and it uses < (less than) operator instead of NOT IN

SELECT MAX(Salary) FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees)

// output: 342000

2. Using Limit Clause:

To find the second highest salary without sub-query, you can use use LIMIT and ORDER BY clause.

SELECT Salary FROM Employees ORDER BY Salary DESC LIMIT 1,1

// Output: 342000

The query will simply sort the salary field in descending order and fetch the second row (2nd largest) from the result set. If you have duplicate values in salary column, then use DISTINCT to select only unique values.

You can generalize the limit query to find out the n-th highest salary like this,

SELECT DISTINCT(Salary) FROM Employees ORDER BY Salary DESC LIMIT (n-1),1

3. With Self Join:

Another interesting way to write the query is to use self join. It takes up the cross-product of the table with itself, exclude the highest salary from the lot and then get the maximum salary from the remaining rows - which gives you the second-maximum.

Select MAX(Emp1.Salary) FROM Employees Emp1, Employees Emp2 WHERE Emp1.Salary < Emp2.Salary

// output: 342000

http://www.kodingmadesimple.com/2018/03/find-second-highest-salary-mysql.html?utm_content=bufferf1a46&utm_medium=social&utm_source=facebook.com&utm_campaign=buffer

Read Also:

• How to Take Backup and Restore MySQL Database
• AJAX Pagination using PHP PDO, MySQL & jQuery

How to change MySQL server time zone

Written by Jacob Nicholson

Views: 110,933

Published: Nov 26, 2012

Comments: 8

In this article we'll discuss how you can change the MySQL time zone on your server so that data stored in your databases will by default use the time zone that you have specified.

A lot of the time your local time zone will be different than the MySQL server's time zone, and this could make working with data inside your databases more difficult for you. Using the following steps you can update your MySQL server's time zone so that it matches your own to make working with this data easier.

This change would require root access to either a VPS (Virtual Private Server) or dedicated server hosting plan, or you can contact our support department to have this changed on your server. You might also simply need to know how to convert MySQL time which wouldn't require root access and can be done on a shared server.

Changing the Time Zone in MySQL

1. Login to your server via SSH as the root user.
2. You can view MySQL's current time zone settings using the following command from the console:
   
   mysql -e "SELECT @@global.time_zone;"
   By default you should get back something similar to:

   +--------------------+
   | @@global.time_zone |
   +--------------------+
   | SYSTEM             |
   +--------------------+
   

   This is because by default your MySQL time zone will be set to the server's default SYSTEM time. If you're interested in changing the entire server's time zone this can be accomplished by setting the time zone in WHM.
3. You can see the server's SYSTEM time stamp using the following command:
   
   date
   Which will give back:
   Mon Nov 26 12:50:07 EST 2012
   
4. You can see the current time stamp reported by the MySQL server using the following command:
   
   mysql -e "SELECT NOW();"
   This should give back the current time stamp:

   +---------------------+
   | NOW()               |
   +---------------------+
   | 2012-11-26 12:50:15 |
   +---------------------+
   

5. Now you can edit your MySQL configuration file with your favorite text editor:
   
   vi /etc/my.cnf
   Then add the following line to change from EST (GMT -5:00) to CST (GMT -6:00):
   default-time-zone = '-06:00'
   Now save the /etc/my.cnf file with your new default.
6. To make the change active you'll want to restart the MySQL service with the following command:
   
   service mysql restart
   
7. Now if you try to see the global time zone setting again with the command:
   
   mysql -e "SELECT @@global.time_zone;"
   You should now get back your new default:

   +--------------------+
   | @@global.time_zone |
   +--------------------+
   | -06:00             |
   +--------------------+
   

8. You should also see now that the NOW() function has updated as well:
   
   mysql -e "SELECT NOW();"
   This should give back the current time stamp:

   +---------------------+
   | NOW()               |
   +---------------------+
   | 2012-11-26 11:50:15 |
   +---------------------+
   

   
   

You should now know how to update the MySQL server's time zone setting, to help make sure the data stored in databases is easy for you to work with. You can also used named time zones instead of the GMT -6:00 format, but this would first require you loading the time zone tables into the mysql database. More information on this can be found on the MySQL site regarding mysql_tzinfo_to_sql and loading the time zone tables.