Select only rows by join tables max value

Question

I want to select only the row which has max b.enddate for u.classno, u.userno. But it doesn't work.

select u.classno, u.userno, b.enddate
from libUser u
join book b on b.id = u.bookid
group by u.classno, u.userno
having b.enddate=max(b.enddate) //doesn't works

score 61 · Answer 1 · edited Jan 04 '17 at 07:26

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.

Nothing wrong with any of it, I was just impressed by the speed. — Vatev, Sep 09 '13 at 08:35
@thenaglecode It depends on the data you have, but most of the time the uncorrelated subquery is the fastest for me (in MySQL that is, it also depends on the DBMS you're using). — fancyPants, Dec 31 '14 at 08:29
Using uncorrelated subquery in my MySql app with great results. — Michael K, May 09 '17 at 21:04

score 10 · Accepted Answer · answered Sep 09 '13 at 08:19

10

Whats wrong with:

select u.classno, u.userno, MAX(b.enddate)
from libUser u
join book b on b.id = u.bookid
group by u.classno, u.userno

answered Sep 09 '13 at 08:19

Filip

656
4
8

8

This answer is **perfectly correct**. But in case someone needs this: This approach isn't always reliable as it will pick **any** row value for non-groped columns viz. `u.classno` and `u.userno` **if they are not included in the group by clause** the value may not be from the same row as `MAX(b.enddate)` is picked from. Usually, it is the first row from each `group` that may change depending on the `order by` clause. I verified this on `MySQL` before writing here. MS-SQL does not allow such query at all. Don't know about other RDBMS. – Izhar Aazmi Apr 22 '15 at 09:57

Select only rows by join tables max value

2 Answers2

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

Linked

Related