Question:

 ------------- --------- 
| Column Name | Type    |
 ------------- --------- 
| id          | int     |
| name        | varchar |
 ------------- --------- 

id is the primary key column for this table. Each row of this table indicates the name and the ID of a student. id is a continuous increment.

Can someone pls explain me the purpose of "id = (select max(id) from seat)" in the following code?

select
(CASE when (select max(id) from seat)%2 = 1 and id = (select max(id) from seat) then id
when id%2 = 1 then id 1
else id -1
end) as id,student
from seat
order by id

Shouldn't the following suffice for this question?

 select
(CASE when (select max(id) from seat)%2 = 1 then id
when id%2 = 1 then id 1
else id -1
end) as id,student
from seat
order by id

CodePudding user response：

It appears that the purpose is to swap IDs between each pair of rows.

If there are an even number of rows, it's no problem, you add 1 to the odd ID, and subtract from the even ID.

But if there are an odd number of rows, you have to treat the last row specially, because there's no next row to swap IDs with. All the other rows get swapped just as in the even case.

In your version of the query, if there are an odd number of rows, you don't swap any IDs, because you aren't checking whether the current row is the last row that should be treated specially.

That's what AND id = (select MAX(id) FROM seat) checks.