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 ?
Advertisement
Answer
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;