how to sort order of LEFT JOIN in SQL query?
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;
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
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'
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'