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