I was trying to getting the first name, last name and salary as of the last signed contract with the procedure below DELIMITER $$ CREATE PROCEDURE last_contract_salary (in p_emp_no int) BEGING SELECT e.emp_no, e.first_name, e.last_name, s.salary, max(s.from_date) FROM employees e JOIN salaries s on e.emp_no = s.emp_no WHERE e.emp_no = p_emp_no; END $$ DELIMITER ; When I executed the procedure, it's giving me what I wanted except that it was picking the salary as of the first contract date. I tried putting HAVING after the where clause but I still got the same output. DELIMITER $$ CREATE PROCEDURE last_contract_salary (in p_emp_no int) BEGING SELECT e.emp_no, e.first_name, e.last_name, s.salary FROM employees e JOIN salaries s on e.emp_no = s.emp_no WHERE e.emp_no = p_emp_no HAVING max(s.from_date); END $$ DELIMITER ;

Hello Yinka, I think I managed to answer your concern, where the procedure would return the most recent salary based on from_date column. First, let me show the query that returns the max salary based on from_date. I decided to include all the other fields you wanted to simplify the query in the procedure later on: SELECT t1.emp_no, e.first_name, e.last_name, s.salary FROM (SELECT emp_no, MAX(from_date) AS from_date FROM salaries GROUP BY emp_no) AS t1 JOIN salaries s ON s.emp_no = t1.emp_no JOIN employees e ON e.emp_no = t1.emp_no WHERE s.from_date = t1.from_date; Then I just included the above as a Table subquery in the stored procedure: DELIMITER $$ CREATE PROCEDURE last_contract_salary(in p_emp_no INT) BEGIN SELECT * FROM ( SELECT t1.emp_no, e.first_name, e.last_name, s.salary FROM (SELECT emp_no, MAX(from_date) AS from_date FROM salaries GROUP BY emp_no) AS t1 JOIN salaries s ON s.emp_no = t1.emp_no JOIN employees e ON e.emp_no = t1.emp_no WHERE s.from_date = t1.from_date ) AS T WHERE T.emp_no = p_emp_no; END $$ DELIMITER ; Since MAX(salary) are in the same line as the MAX(from_date) for each employee, the result would just be the same as Tsvetelin's. However, I highly recommend that you UPDATE the database to provide a data with your description (max from_date but not max salary) to check if this works. I hope this helps, Carl

Hi Yinka! Thanks for reaching out. Actually, you do not need MAX(s.from_date), you need MAX(s.salary). Please, use the following code: DELIMITER $$ CREATE PROCEDURE last_contract_salary (in p_emp_no int) BEGIN SELECT e.emp_no, e.first_name, e.last_name, MAX(s.salary) FROM employees e JOIN salaries s on e.emp_no = s.emp_no WHERE e.emp_no = p_emp_no``; END $$ DELIMITER ; Hope this helps. Best, Tsvetelin

Thanks, Tsvetelin. It helped. What if I want to get the salary as of the last contract. Let's imagine the 2019/2020 pandemic that some companies were laying of while cutting others salary and then further imaging that that was the last contract till date. How does one go about combining the last date (which is definitely the higher and MAX can be used) and the last salary which might not be the highest (hence, MAX is not an option).