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

User · Answer

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'

User · Answer

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

User · Answer

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

User · Answer

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

User · 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

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

Examples related to mysql

Examples related to sql

Examples related to greatest-n-per-group