I have 2 tables that I would like join where the contactid is unique and has the highest allowance.

EMPLOYEE

EMPLOYEEDETAILS

I would like to achieve below where I want to show the EmployeeID with the highest allowance for the unique contactid.

I have tried the SQL code below but it's not giving me the unique contactid with the highest allowance.

SELECT  EMPLOYEE.employeeid, EMPLOYEE.contactid, MAX(EMPLOYEEDETAILS.annualallowance)
from
cxm_employee EMPLOYEE
JOIN
cxm_employeedetails EMPLOYEEDETAILS ON EMPLOYEE.employeeid = EMPLOYEEDETAILS.employeeid
group by EMPLOYEE.employeeid,EMPLOYEE.employeecontactid

Where am I going wrong?

CodePudding user response：

I want to show the EmployeeID with the highest allowance for the unique contactid.

Your code does not work because it only brings the details of the current employee, letting along other employees that have the same contactid.

I think it is easier to do with window functions:

select *
from (
    select e.*, d.annualallowance,
        rank() over(partition by e.contactid order by d.annualallowance desc) rn
    from employee e
    inner join employeedetails d on d.employeeid = e.employeeid
) t
where rn = 1

rank() ranks employees having the same contact by descending annual allowance ; we can then use this information for filtering.

In pre-8.0 versions of MySQL, where window functions are not supported, an alternative uses a correlated subquery to retrieve the top allowance per contact; we can then use this information to filter the dataset :

select e.*, d.annualallowance
from employee e
inner join employeedetails d on d.employeeid = e.employeeid
where d.annualallowance = (
    select max(d1.annualallowance)
    from employee e1
    inner join employeedetails d1 on d1.employeeid = e1.employeeid
    where e1.contactid = e.contactid
)

Demo on DB Fiddle