CODEWITHSUNDEEP
mysqlsql
Aleksandar Petrovic
Aleksandar Petrovic

Reputation: 13

Select maximum value from one column by second column

I have two tables:

EMPLOYEES
=====================================================
ID    NAME    SUPERVISOR    LOCATION    SALARY
-----------------------------------------------------
34       John                  AL          100000
17       Mike    34            NY          75000
5        Alan    34            LE          25000
10       Dave    5             NY          20000

BONUS
========================================
ID        Bonus
----------------------------------------
17        5000
34        5000
10        2000

I have to write query which return a list of the highest paid employee in each location with their names, salary and salary+bonus. Ranking should be based on salary plus bonus. So I wrote this query:

select em.name as name, em.salary as salary, bo.bonus as bonus, max(em.salary+bo.bonus) as total
from employees as em
join bonus as bo on em.empid = bo.empid 
group by em.location

But I'm getting wrong names and query don't return one employee without bonus (empid = 5 in employees table) which have highest salary based by location (25000 + 0 bonus).

Upvotes: 1

Views: 152

Answers (4)

Suman Banerjee
Suman Banerjee

Reputation: 1961

Please try this: 

 select em.name as name, em.salary as salary,ISNULL(bo.bonus,0) as bonus,
     max(em.salary+ISNULL(bo.bonus,0)) as total
    from employees as em
    left join bonus as bo on em.ID = bo.ID 
    group by  em.name,em.salary, bo.bonus order by MAX(em.salary+ISNULL(bo.bonus,0)) Desc

Upvotes: 0

jpw
jpw

Reputation: 44921

You can either do

select 
  em.location, 
  em.name as name, 
  em.salary as salary, 
  IFNULL(bo.bonus,0)) as bonus, 
  max(em.salary+IFNULL(bo.bonus,0)) as total
from employees as em
left join bonus as bo on em.empid = bo.empid 
group by em.location;

This query however relies on a group by behavior that is specific to MySQL and would fail in most other databases (and also in later versions of MySQL if the setting ONLY_FULL_GROUP_BY is enabled).

I would suggest a query like below instead:

select 
  em.location, 
  em.name as name, 
  em.salary as salary, 
  IFNULL(bo.bonus,0)) as bonus, 
  highest.total
from employees as em 
left join bonus as bo on em.empid = bo.empid 
join (
    select 
      em.location, 
      max(em.salary+IFNULL(bo.bonus,0)) as total
    from employees as em 
    left join bonus as bo on em.empid = bo.empid 
    group by em.location
) highest on em.LOCATION = highest.LOCATION and em.salary+IFNULL(bo.bonus,0) = highest.total;

Here you determine the highest salary+bonus for each location and use that result as a derived table in a join to filter out the employee with highest total for each location.

See this SQL Fiddle

Upvotes: 1

rtruszk
rtruszk

Reputation: 3922

It should be:

select 
   em.name as name, 
   em.salary as salary, 
   COALESCE(bo.bonus,0) as bonus, 
   max(em.salary + COALESCE(bo.bonus,0) ) as total
from employees as em
left join bonus as bo on em.empid = bo.empid 
group by em.location

You can check it in SQLFiddle

Upvotes: 0

SverreN
SverreN

Reputation: 177

Maybe try using a left join:

select em.name as name, em.salary as salary, bo.bonus as bonus, max(em.salary+bo.bonus) as total
from employees as em
left join bonus as bo on em.empid = bo.empid 
group by em.location

Upvotes: 1

Related Questions