MySQL: Finding the Details of a Maximum Element

  Recently I have come across a table of data containing 4 fields. The fields are ENAME, ID, SAL and DEPT, Where ENAME stores the employee name, ID stores the employee ID, SAL stores the salary and DEPT stores the department. The table with sample data is shown below.

TABLE 1

  I was supposed to find out the name, department, and salary of the employee with maximum salary from the given table. I used the following query.

Select ENAME, DEPT, MAX(SAL) from employee;         # Wrong Query

To my horror, I received the following output when applied on Table 1.

Wrong Output

At first I was confused. But then I have referred Table 2 given below.

TABLE 2

  I understood the mistake after referring Table 2. The reason was very simple. Max( ) returns the maximum salary, but it doesn't retrieve any other data corresponding to the maximum value. In order to get the correct result use the following query. 

Select ENAME, DEPT, MAX(SAL) from employee where SAL in
(Select MAX(SAL) from employee);        # Correct Query

The following correct output is obtained when the query is applied on Table 2

.

Correct Output