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

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 ?

Older MySQL versions this is enough:

SELECT
    `userName`,
    `carPrice`
FROM `users`
LEFT JOIN (SELECT * FROM `cars` ORDER BY `carPrice`) as `cars`
ON cars.belongsToUser=users.id
WHERE `id`='4'

Nowdays, if you use MariaDB the subquery should be limited.

SELECT
    `userName`,
    `carPrice`
FROM `users`
LEFT JOIN (SELECT * FROM `cars` ORDER BY `carPrice` LIMIT 18446744073709551615) as `cars`
ON cars.belongsToUser=users.id
WHERE `id`='4'

try this out:

   SELECT
      `userName`,
      `carPrice`
   FROM `users`
   LEFT JOIN `cars`
   ON cars.belongsToUser=users.id
   WHERE `id`='4'
   ORDER BY `carPrice` DESC
   LIMIT 1

Felix

Several other answer give the solution using MAX. In some scenarios using an agregate function is either not possilbe, or not performant.

The alternative that I use a lot is to use a correlated sub-query in the join...

SELECT
   `userName`,
   `carPrice`
FROM `users`
LEFT JOIN `cars`
ON cars.id = (
  SELECT id FROM `cars` WHERE BelongsToUser = users.id ORDER BY carPrice DESC LIMIT 1
)
WHERE `id`='4'

This will get you the most expensive car for the user:

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

However, this statement makes me think that you want all of the cars prices sorted, descending:

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

So you could try this:

SELECT users.userName, cars.carPrice
FROM users
LEFT JOIN cars ON cars.belongsToUser=users.id
WHERE users.id=4
GROUP BY users.userName
ORDER BY users.userName ASC, cars.carPrice DESC