Showing more fields with MAX

Question

Hi, I was watching the lecture regarding the MAX function and with the codes given in the lecture, the output would be like this:

I was wondering, what if we want to show more columns? For example, we want to show also the corresponding emp_no. Any help? Thanks

Answer 1

Hi Lisa! Thanks for reaching out. Please accept my apologies for the delayed response. There are perhaps even more solutions but please consider the following two suggestions. Basically, we need to either use a subquery in order to specify the employee with what salary we are interested in.

SELECT 
MIN(s.salary), e.first_name
FROM
employees e
JOIN
(SELECT 
salary, emp_no
FROM
salaries
ORDER BY salary ASC
LIMIT 1) s ON e.emp_no = s.emp_no;

Alternatively, we can take advantage of the ORDER BY clause to obtain/find the same value.

SELECT 
e.emp_no, s.salary, e.first_name, e.last_name
FROM
salaries s
JOIN
employees e ON s.emp_no = e.emp_no
ORDER BY salary ASC
LIMIT 1;

Hope this helps.
Best,
Martin Please feel free to use these queries and adjust them in a way that will deliver precisely the information you are interested in.

Showing more fields with MAX

Submit an answer