MySQL greatest n per group only works with more than one row

Question

I have the following book table

id	time	teacher	book	class
1	22-05-25 03:20:39	21	null	1
2	22-05-25 03:20:39	21	7853	2
3	22-05-25 03:20:39	21	7854	2
4	22-05-25 03:20:39	21	7855	2
5	22-05-25 03:20:39	21	7856	2

and the following SQL

SELECT t0_.*    
FROM book t0_
    LEFT JOIN book t1_ ON (t0_.teacher= t1_.teacher AND t0_.id < t1_.id)
WHERE
    t0_.book IS NOT NULL AND
    t0_.teacher IN (21) AND
    t1_.id IS NULL;

This works as expected, returning id 5. But when t0_.book IS NOT NULL is changed to t0_.book IS NULL, I get blank results instead of id 1. So I tried another approach

SELECT t0_*, MAX(t0_.id) AS uid
FROM book t0_
WHERE
    t0_.book IS NOT NULL AND
    t0_.teacher IN (21) AND
GROUP BY t0_.time

This time I got id 2 instead of id 5. How can I get the greatest row when targeting records with one or more rows?

EDIT: To further illustrate the problem and what is desired, am looking for the greatest teacher from each class where book associated with the teacher is null. Ordinarily it can a number or not null. With the first query, I've done tests and found that it works as expected, but only where rows being targeted are more than one. So naturally, my assumptive interpretation was that, since a left join is being used, it could be that a lone row cannot be compared with itself. Hence the move to the second solution - which appears to work, but not quite.

How can I get the greatest teacher per class where book is null? I don't know if I need to further clarify.

please always copy and paste your actual working queries; typos really make it harder to know what you are looking for — ysth, May 25 '22 at 17:06
start with a problem statement of what you are trying to achieve. are you looking for the row with the greatest id for each time? the row with the greatest time for each teacher? something else? — ysth, May 25 '22 at 17:09

score 1 · Accepted Answer · answered May 25 '22 at 17:21

You need to restrict t1_ to just the other rows with book = NULL. Otherwise, you're joining the null row with the non-null rows, and there will always be a match, so the t1_.id IS NULL check will fail.

Add the condition AND t1_.book IS NULL to the ON condition.

SELECT t0_.*    
FROM book t0_
    LEFT JOIN book t1_ ON (t0_.teacher= t1_.teacher AND t0_.id < t1_.id) 
        AND t1_.book IS NULL
WHERE
    t0_.book IS NULL AND
    t0_.teacher IN (21) AND
    t1_.id IS NULL;

DEMO

MySQL greatest n per group only works with more than one row

1 Answers1