The 365 Data Science team is proud to invite you to our own community forum. A very well built system to support your queries, questions and give the chance to show your knowledge and help others in their path of becoming Data Science specialists.
Ask
Anybody can ask a question
Answer
Anybody can answer
Vote
The best answers are voted up and moderated by our team

Showing more fields with MAX

Showing more fields with MAX

Super Learner
0
Votes
1
Answer

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
 

1 Answer

365 Team
0
Votes

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.

×
EXTENDED SALE
Learn Data Science this Summer!
Get 50% OFF