MySQL: Finding the Details of Maximum Elements in Every Group from a Table

  The table for our discussion is called emp and contains 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.

Sample Table

  

  The Task was to find the NAME, DEPARTMENT and SALARY of the employee having highest salary in each Department. I used the Query given below to solve the problem. 

select 

    ENAME, DEPT, Max(SAL)

from

    emp

group by DEPT;        # Wrong Query

But the output was wrong to my dismay. The incorrect output is shown below.

Wrong Output

I used many different Queries and finally got the correct query to solve the problem. The correct query and output is shown below.

 select 

    e.ENAME, e.DEPT, e.SAL
from
    emp e
        join
    (select 
        DEPT, MAX(SAL) MS
    from
        emp
    group by DEPT) m ON e.DEPT = M.DEPT and e.SAL = m.MS        # Correct Query

Correct Output

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

Implementing MINUS Query in MySQL

  The SQL MINUS query returns all rows in the first SQL SELECT statement that are not returned in the second SQL SELECT statement. Each SQL SELECT statement within the SQL MINUS query must have the same number of fields in the result sets with similar data types.

The syntax for the SQL MINUS query is:

select field1, field2, ... field_n 
from tables1 
MINUS 
select field1, field2, ... field_n 
from table2; 

But this query is not implemented in MySQL. So to achieve the same effect you can use the following Query statement.  

select field1, field2, ..., field_n 
from table1 
where (field_x) NOT IN (select field_x from table2); 

Where field_x is the field over which the comparison is performed. 

Implementing INTERSECT Query in MySQL

  The SQL INTERSECT query returns the results of 2 or more "select" queries. However, it only returns the rows selected by all queries. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results. 

The syntax for the SQL INTERSECT query is:

select field1, field2, ..., field_n 
from table1 
INTERSECT 
select field1, field2, ..., field_n
from table2
 

But this query is not implemented in MySQL. So to achieve the same effect you can use the following Query statement.  

select field1, field2, ..., field_n 
from table1 
where (field_x) IN (select field_x from table2);

Where field_x is the field over which the comparison is performed.