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.
CodePudding user response:
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;