Select only rows by join tables max value

by

Here is an excellent article in the official MySQL documentation, but only standard SQL is used there, so it can be applied to whatever RDBMS you are using.

The Rows Holding the Group-wise Maximum of a Certain Column

Task: For each article, find the dealer or dealers with the most
expensive price.

This problem can be solved with a subquery like this one:

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article);

The preceding example uses a correlated subquery, which can be
inefficient (see Section 13.2.10.7, “Correlated Subqueries”). Other
possibilities for solving the problem are to use an uncorrelated
subquery in the FROM clause or a LEFT JOIN.

Uncorrelated subquery:

SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
  SELECT article, MAX(price) AS price
  FROM shop
  GROUP BY article) AS s2
  ON s1.article = s2.article AND s1.price = s2.price;

LEFT JOIN:

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL;

The LEFT JOIN works on the basis that when s1.price is at its maximum
value, there is no s2.price with a greater value and the s2 rows
values will be NULL.

More Related Contents:

  1. What is the result of following SQL Query?
  2. What is the difference between “INNER JOIN” and “OUTER JOIN”?
  3. Join vs. sub-query
  4. Can I use CASE statement in a JOIN condition?
  5. What is the difference between Left, Right, Outer and Inner Joins? [duplicate]
  6. SQL join: selecting the last records in a one-to-many relationship
  7. Subqueries vs joins
  8. Does the join order matter in SQL?
  9. Difference between left join and right join in SQL Server [duplicate]
  10. What is the difference between JOIN and UNION?
  11. Filter Table Before Applying Left Join
  12. SQL Join Differences
  13. Difference between natural join and inner join
  14. Are “from Table1 left join Table2” and “from Table2 right join Table1” interchangeable?
  15. Natural join in SQL Server
  16. Including null values in an Apache Spark Join
  17. Is a JOIN faster than a WHERE?
  18. What is semi-join in database?
  19. ORA – 00933 confusion with inner join and “as”
  20. MySQL: GROUP_CONCAT with LEFT JOIN
  21. Joining multiple tables in SQL
  22. MySQL: “The SELECT would examine more than MAX_JOIN_SIZE rows”
  23. What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?
  24. sql sum data from multiple tables
  25. Why do multiple-table joins produce duplicate rows?
  26. SQL server join tables and pivot
  27. SQL Inner Join On Null Values
  28. INNER JOIN vs multiple table names in “FROM” [duplicate]
  29. Re-Writing “Fuzzy Join” Functions from R to SQL
  30. Procedurally transform subquery into join

Leave a Comment