SQL max function leetcode 626

Question

In Leetcode 626. Exchange Seats

I write

Select
 (case
 when id%2 != 0 and id = (select max(id) from Seat) then id
 when id%2 =0 then id-1
 else id+1 end) as id,
 student
From Seat
order by id ASC

But what's the difference between

id = (select max(id) from Seat)

and

id = max(id)

It turns out to be wrong when I change it to id = max(id), but I don't understand why. Don't they have the same meaning?

Could someone please explain it to me?

• GSerg: dbfiddle.uk/Ml1VCSNz
• Tim Biegeleisen: The id = max(id) version would only run on older versions of MySQL - don't ever do that

Answer 1

All columns in the SELECT clause that do not have an aggregate need to be in the GROUP BY

(also see stackoverflow#6467216)

Therefor "id = max(id)" is only valid if you add a "group by" clause

select
 (case
 when id%2 != 0 and id = max(id) then id
 when id%2 =0 then id-1
 else id+1 end) as id,
 student
from Seat
group by id, student
order by id ASC

Now with the "id = (select max(id) from seat)" part...

select max(id) from seat
 -- is the same as
select max(id) from seat group by id
 -- and not the same as (a bit stipe down from original sql without the case statement)
select id, student, max(id) from seat group by id, student