CODEWITHSUNDEEP
mysqlsqlcommand-line
Morphey
Morphey

Reputation: 65

MYSQL - returning only max result from an ID

I wasn't quite sure how to title it as its quite a strange question. I'm building a slightly advanced query and it all it works fine except it returns all information under that ID

Below is my code:

select surname, salary_per_year, position _held, grade, name 
from person inner join career_history on person.person_id = career_history.person_id
join grade on grade.person_id = career_history.person_id
join school on grade.school_id = school.school_id
where salary_per_year > 20000 and grade >65;

This creates this table. 

+----------+-----------------+--------------------+-------+---------------------------+
| surname  | salary_per_year | position_held      | grade | name                      |
+----------+-----------------+--------------------+-------+---------------------------+
| webster  |           32000 | data analyst       |    78 | Oxford University         |
| webster  |           23000 | data analyst       |    78 | Oxford University         |
| molina   |           21000 | receptionist       |    81 | UNAM                      |
| jones    |           22000 | law assistant      |    69 | University of Queensland  |
| jones    |           39000 | junior lawyer      |    69 | University of Queensland  |
| lovely   |           26000 | junior analyst     |    71 | Univesity of Bristol      |
| clark    |           23000 | junior banker      |    68 | Harvard Univeristy        |
| clark    |           65000 | head banker        |    68 | Harvard Univeristy        |
| roberts  |           26000 | researcher         |    69 | MIT                       |
| roberts  |           32000 | teacher            |    69 | MIT                       |
| knght    |           28000 | head of department |    92 | University of Barcelona   |
| knght    |           39000 | campus director    |    92 | University of Barcelona   |
| yanagowa |           39000 | database manager   |    86 | Oxford Brookes Univeristy |
| yanagowa |           55000 | head of data       |    86 | Oxford Brookes Univeristy |
+----------+-----------------+--------------------+-------+---------------------------+

My question is how do I make it only show the highest salary per surname. e.g

yanagowa 55000 head of data 86 Oxford Brookes University not Yanagowa 39000 database maanger 86 oxford brookes univeristy

please could someone point me in the right direct!

Upvotes: 1

Views: 83

Answers (2)

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7242

Give this a try.

SELECT surname, max(salary_per_year) as maxSalary, position_held, grade, name 
FROM person
INNER JOIN career_history on person.person_id = career_history.person_id
JOIN grade on grade.person_id = career_history.person_id
JOIN school on grade.school_id = school.school_id
WHERE salary_per_year > 20000 and grade > 65
GROUP BY surname
ORDER BY Max(salary_per_year) DESC;

EDIT

The ORDER BY can be shortened actually, this was pointed out after i posted it.

ORDER BY maxSalary DESC;

Upvotes: 2

Vahe Shadunts
Vahe Shadunts

Reputation: 1966

Using group by

Note: all fields selected must have a group function(e.g. max, min, sum, avg) if your'e groupping them 

Select surname, max(salary_per_year) from @yourtable
group by surname

Upvotes: 0

Related Questions