Get Employee with highest Salary in each Department

Question

I am writing a simple HQL to find out the employee who is having highest salary based on department. In the output I am expecting employee name, department name and highest salary.

Here are my entities:

Employee.java

@Entity
public class Employee implements java.io.Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;
    private String name;
    @ManyToOne
    @JoinColumn(name = "deptartment_id")
    private Department dept;
    @OneToOne
    @JoinColumn(name="emp_salary")
    private Salary salary;
}

Department.java

@Entity
public class Department {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;
    private String name;
    @OneToMany(mappedBy="dept")
    private Set<Employee> employees = new HashSet<Employee>();
}

Salary.java

@Entity
public class Salary {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;
    private int salary;
}

If I run below HQL query :

List<Object[]> results = session.createQuery(
                "select d.name, max(s.salary) "
                + "from Employee e join e.dept d join e.salary s "
                + "group by d.name").list();

I am getting maximum salary for each department. This is good, but I need employee name also in the output.

So, if I change my HQL to :

session.createQuery(
                "select e.name, d.name, max(s.salary) "
                + "from Employee e join e.dept d join e.salary s "
                + "group by d.name, e.name").list();

This query is returning all the employees in my database.

Can you please tell me what is the correct HQL and explain what is the issue with my HQL?

This is my code for saving employee records:

Employee e1 = new Employee();
        e1.setName("A1");
        Salary salary1 = new Salary();
        salary1.setSalary(10000);
        e1.setSalary(salary1);
        Department dept1 = new Department();
        dept1.setName("Operations");
        e1.setDept(dept1);

        Employee e11 = new Employee();
        e11.setName("A2");
        Salary salary11 = new Salary();
        salary11.setSalary(12000);
        e11.setSalary(salary11);
        Department dept11 = new Department();
        dept11.setName("Operations");
        e11.setDept(dept11);

        Employee e12 = new Employee();
        e12.setName("A3");
        Salary salary12 = new Salary();
        salary12.setSalary(15000);
        e12.setSalary(salary12);
        Department dept12 = new Department();
        dept12.setName("Operations");
        e12.setDept(dept12);

        Employee e2 = new Employee();
        e2.setName("B1");
        Salary salary2 = new Salary();
        salary2.setSalary(20000);
        e2.setSalary(salary2);
        Department dept2 = new Department();
        dept2.setName("IT");
        e2.setDept(dept2);

        Employee e21 = new Employee();
        e21.setName("B2");
        Salary salary21 = new Salary();
        salary21.setSalary(22000);
        e21.setSalary(salary21);
        Department dept21 = new Department();
        dept21.setName("IT");
        e21.setDept(dept21);

        Employee e22 = new Employee();
        e22.setName("B3");
        Salary salary22 = new Salary();
        salary22.setSalary(25000);
        e22.setSalary(salary22);
        Department dept22 = new Department();
        dept22.setName("IT");
        e22.setDept(dept22);

        Employee e3 = new Employee();
        e3.setName("C1");
        Salary salary3 = new Salary();
        salary3.setSalary(30000);
        e3.setSalary(salary3);
        Department dept3 = new Department();
        dept3.setName("Software");
        e3.setDept(dept3);

        Employee e31 = new Employee();
        e31.setName("C2");
        Salary salary31 = new Salary();
        salary31.setSalary(32000);
        e31.setSalary(salary31);
        Department dept31 = new Department();
        dept31.setName("Software");
        e31.setDept(dept31);

        Employee e32 = new Employee();
        e32.setName("C3");
        Salary salary32 = new Salary();
        salary32.setSalary(35000);
        e32.setSalary(salary32);
        Department dept32 = new Department();
        dept32.setName("Software");
        e32.setDept(dept32);

Testy

I have no idea about HQL but if it is similiar to SQL there should be limit like; SELECT column_name(s) FROM table_name LIMIT number; – Tehuantepec

@kaanyılmaz, we cannot use limit in HQL. I got error saying unexpected token: limit – Testy

If you used JPQL, it would be possible to create the LIMIT expression using Query.setMaxResults(). Since Hibernate is JPA-compliant, I'd be surprised if there isn't an API function to achieve it with HQL. – Inedited

Answer 1

You can try a window function to get the employee name besides the salary

List<Object[]> results = session.createQuery(
                "select d.name AS department, "
                + "LAST_VALUE(e.name) OVER ("
                + " PARTITION BY d.name"
                + " ORDER BY s.salary"
                + ") AS employee_name,"
                + "LAST_VALUE(e.name) OVER ("
                + " PARTITION BY d.name"
                + " ORDER BY s.salary"
                + ") AS max_salary"
                + "from Employee e join e.dept d join e.salary s ").list();

Gaullist