Why my code only return 1 row? Leetcode#626
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.