mysql - how to sort order of LEFT JOIN in SQL query?

Question

OK I tried googling for an answer like crazy, but I couldn't resolve this, so I hope someone will be able to help.

Let's say I have a table of users, very simple table:

id | userName
3    Michael
4    Mike
5    George

and I have another table of their cars and their prices.

id | belongsToUser | carPrice
1    4               5000
2    4               6000
3    4               8000

Now what I need to do is something like this (feel free to rewrite):

   SELECT
      `userName`,
      `carPrice`
   FROM `users`
   LEFT JOIN `cars`
   ON cars.belongsToUser=users.id
   WHERE `id`='4'

Which returns:

Mike | 5000

But I need the most expensive car of a certain user, not the first entry found.

So question: How do I set the LEFT JOIN table to be ordered by carPrice, DESC ?

Answer 1

Try using MAX with a GROUP BY.

SELECT u.userName, MAX(c.carPrice)
FROM users u
    LEFT JOIN cars c ON u.id = c.belongsToUser
WHERE u.id = 4;
GROUP BY u.userName;

---

Further information on GROUP BY

The group by clause is used to split the selected records into groups based on unique combinations of the group by columns. This then allows us to use aggregate functions (eg. MAX, MIN, SUM, AVG, ...) that will be applied to each group of records in turn. The database will return a single result record for each grouping.

For example, if we have a set of records representing temperatures over time and location in a table like this:

Location   Time    Temperature
--------   ----    -----------
London     12:00          10.0
Bristol    12:00          12.0
Glasgow    12:00           5.0
London     13:00          14.0
Bristol    13:00          13.0
Glasgow    13:00           7.0
...

Then if we want to find the maximum temperature by location, then we need to split the temperature records into groupings, where each record in a particular group has the same location. We then want to find the maximum temperature of each group. The query to do this would be as follows:

SELECT Location, MAX(Temperature)
FROM Temperatures
GROUP BY Location;