0

In this database:

Happy Insurance

I need to write an SQL query that will display the name of each client of the agent with the highest agent rating in the company.

What I'm trying right now is,

SELECT ClientName
FROM CLIENT.ClientName
WHERE CLIENT.AgentID = AGENT.AgentID AND MAX(AGENT.AgentRating);

I'm new to MySQL, so I just want to check if I'm using the MAX and AND operators properly, or if there's a simpler way to do this.

Barmar
  • 741,623
  • 53
  • 500
  • 612
H Watson
  • 3
  • 2
  • 1
    You're not comparing `MAX(AGENT.AgentRating)` with anything. – Barmar Mar 23 '21 at 01:50
  • Write a subquery that gets the agent with the max rating for each group. Join that with the `Client` table. See https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql for the first part. – Barmar Mar 23 '21 at 01:52

1 Answers1

0

Try using a subquery to select the highest rated agent, then join it to your CLIENT table to select the names of the associated clients. Something like this:

SELECT ClientName
FROM CLIENT.ClientName
JOIN (
    SELECT AgentID FROM AGENT ORDER BY AgentRating DESC LIMIT 1   
) sq ON sq.AgentID = CLIENT.AgentID

The subquery (SELECT AgentID FROM AGENT ORDER BY AgentRating DESC LIMIT 1) sq selects the AgentID column from the AGENT table

Then with ORDER BY AgentRating DESC it orders that column by the AgentRating descending placing the highest rating at the top of the results.

Then the LIMIT 1 limits the rows returned to 1, giving you only 1 (the first returned) record from the AGENT table that we just ordered to put the highest rated agent at the top.

Then when you JOIN that result from the subquery with your CLIENT table on the AgentID, you will only get results in your CLIENT table maching the selected AgentID from the subquery.

Andrew
  • 1,745
  • 1
  • 21
  • 29
  • @Strawberry Thanks for the feedback, I've adjusted the code. – Andrew Mar 23 '21 at 13:46
  • And now you don't need a subquery :-) – Strawberry Mar 23 '21 at 14:53
  • @Strawberry You've lost me now. My answer is based on the use of the subquery, if there's a better way I am interested to know. – Andrew Mar 23 '21 at 15:01
  • `SELECT c.ClientName FROM CLIENT c JOIN AGENT a ON a.AgentID = c.AgentID ORDER BY a.AgentRating DESC LIMIT 1` – Strawberry Mar 23 '21 at 15:15
  • @Strawberry My understanding is that the asker wants the names of **all** clients associated with the highest rated agent: "display the name of each client of the agent with the highest agent rating in the company". You query would limit the results to only one client name would it not? With the sample data provided, I beleive the highest rated agent would be ```A1``` and the results would show ```Tom``` and ```Karen```. – Andrew Mar 23 '21 at 15:25
  • So after the subquery, the last line would just be, `WHERE MAX(AGENT.AgentRating);` Right? – H Watson Mar 23 '21 at 18:02
  • No you should not need anything else, the subquery does this. I will update my answer to include a bit more explanation. – Andrew Mar 23 '21 at 18:08