Why my code only return 1 row? Leetcode#626 I have used MySQL. My code is
SELECT 
(CASE WHEN mod(s.id,2)=0 THEN s.id-1
WHEN mod(s.id,2)=1 AND s.id != MAX(s.id) THEN s.id+1
END) AS id,
s.student
FROM seat s
ORDER BY id

It only returns one row
{&quot;headers&quot;: [&quot;id&quot;, &quot;student&quot;], &quot;values&quot;: [[2, &quot;Abbot&quot;]]}
But if I delete AND s.id != MAX(s.id)
then I will get all rows.
I don't understand it. Is my understanding of MAX function wrong?
You are using an agregate function which causes the result to collapse to single row.
Possible solution:
SELECT 
(CASE WHEN mod(s.id,2)=0 THEN s.id-1
WHEN mod(s.id,2)=1 AND s.id != maxid.maxid THEN s.id+1
END) AS id,
s.student
FROM seat s
join (select max(s.id) as maxId from seat) as maxId on 1=1
ORDER BY id

You are using it in the SELECT, not CONDITIONAL (WHERE).
Now, the question you ask is a good one and i would have to refresh my knowledge of inner workings of SQL.
I think its because the moment it hits MAX, its collapses the result set, as it doesnt &quot;see&quot; a need to return all rows.
Simply said, the query processor doesnt care if its a condition in case statement or value to be returned. It's there and thats that.
Aggregation function without group by is a mysql thing, and not SQL standard, so the behaviour is specific to MySQL.