sql - How to get the maximum value based on join conditions? - Stack Overflow

I have the following sample data:

Table EmployeeWeek:

employee	week
1	15
1	20
2	22
3	7
4	10

I have the following sample data:

Table EmployeeWeek:

employee	week
1	15
1	20
2	22
3	7
4	10

Table Rates:

employee	week	rate
1	14	140
1	15	200
1	17	76
2	4	101
2	22	220
3	12	65

Now, I want the output to be the Employee, Week and the latest available (if any) Rate matching the Employee and being before the Week in the EmployeeWeek table

My output should look like below:

employee	week	rate
1	15	200
1	20	76
2	22	220
3	7
4	10

How to achieve this?

I tried using a MAX(Week) in a LEFT JOIN LIKE this:

SELECT EW.Employee,
       EW.Week,
       R.Rate
FROM EmployeeWeek EW
LEFT JOIN Rates R ON EW.Employee = R.Employee AND EW.Week >= R.Week

but that gives me multiple records per employee. I also tried incorporating a MAX(Week) in the join condition, but then I can't also match on Employee or I get the Rate from a Week higher than the target Week.

How to solve this?

(In reality, the EmployeeWeek table containes a lot of other columns, I only included the relevant ones to make the example as clear as possible)

• sql
• mysql

Share Improve this question Follow edited Feb 3 at 14:26 samhita 4,11522 gold badges1111 silver badges1818 bronze badges asked Feb 3 at 13:57 PeterPeter 78477 silver badges2525 bronze badges 4

• 1 Do a GROUP BY. – jarlh Commented Feb 3 at 14:00
• 1 Please do not upload images of code/data/errors. - Please edit and provide the code/data/errors as text. – DarkBee Commented Feb 3 at 14:01
• EmployeeWeek table containes a lot of other columns - does that mean you SELECT more columns too? – jarlh Commented Feb 3 at 14:01
• It's particularly important to express the version of the DBMS for MySQL, is that pre-8 or 8+, tag it please. – Barbaros Özhan Commented Feb 3 at 22:14

Add a comment  | 

4 Answers 4

Sorted by: Reset to default 2

You can find the latest rate for each employee if it exists by ordering the week and assign a rank, so the rate for the latest week for an employee is ordered first.This is then filtered for 1st rank to get the latest rate.

Sample Query:

 WITH latest_rates AS 
  (
    SELECT ew.employee,ew.week,r.rate,
    ROW_NUMBER() OVER (PARTITION BY ew.employee, ew.week ORDER BY r.week DESC) AS rn
    FROM employee_week ew
    LEFT JOIN rates r ON 
  ew.employee = r.employee AND r.week <= ew.week
)
SELECT employee,week,rate
FROM latest_rates
WHERE  rn = 1 ;

Output

Demo Fiddle

employee	week	rate
1	15	200
1	20	76
2	22	220
3	7	null
4	10	null

If your database supports QUALIFY you can use that to filter for the previous week in EmployeeWeek when joining to Rates

SELECT ew.employee
    , ew.week
    , rate
FROM EmployeeWeek ew
LEFT JOIN Rates r
    ON ew.employee = r.employee
    AND ew.week > r.week
QUALIFY ROW_NUMBER() OVER (PARTITION BY ew.employee, ew.week ORDER BY r.week DESC) = 1
ORDER BY ew.employee
    , ew.week
;

Output:

employee	week	rate
1	15	140
1	20	76
2	22	101
3	7
4	10

You can expand Rates (by CTE or subquery) with calculated column toWeek as next week-1.
And join with EmployeeWekk so week is between fromWeek and toWeek.
For last row of Rates also check or r.toWeek is null - no rate after this week, so this rate applied to all weeks after current.

with RateRanges as(
  select employee,rate, week fromWeek
    ,lead(week)over(partition by employee order by week)-1 toWeek
  from Rates
)
select ew.*,r.rate
from EmployeeWeek ew
left join RateRanges r on r.employee=ew.employee 
  and ew.week >=r.fromWeek and (ew.week<=r.toWeek or r.toWeek is null)

employee	week	rate
1	15	200
1	20	76
2	22	220
3	7	null
4	10	null

Expanded Rates with weeks fromWeek up to toWeek.

  select employee, week fromWeek
    ,lead(week)over(partition by employee order by week)-1 toWeek
    ,rate
  from Rates

employee	fromWeek	toWeek	rate
1	14	14	140
1	15	16	200
1	17	null	76
2	4	21	101
2	22	null	220
3	12	null	65

fiddle

Instead of your regular outer join, you would need a lateral outer join for this, in which you'd select the desired rate. The syntax is a tad ugly, because LEFT [OUTER] JOIN requires an ON or USING clause, while a lateral join would't need any, as the correlation takes place in the subquery. Other DBMS have introduced OUTER APPLY and CROSS APPLY for a more appropriate syntax; in MySQL you'll have to add a dummy ON TRUE instead in order to satisfy SQL syntax requirements.

SELECT
  ew.employee,
  ew.week,
  rr.rate
FROM employeeweek ew
LEFT JOIN LATERAL
(
  SELECT r.rate
  FROM rates r 
  WHERE r.employee = ew.employee
  AND r.week <= ew.week
  ORDER BY r.week DESC
  LIMIT 1
) rr ON TRUE
ORDER BY ew.employee, ew.week;

Or put the subquery into the SELECT clause:

SELECT
  ew.employee,
  ew.week,
  (
    SELECT r.rate
    FROM rates r 
    WHERE r.employee = ew.employee
    AND r.week <= ew.week
    ORDER BY r.week DESC
    LIMIT 1
  ) AS rate
FROM employeeweek ew
ORDER BY ew.employee, ew.week;