Question

Mary is a teacher in a middle school and she has a table seat storing students' names and their corresponding seat ids.

The column id is continuous increment.

Mary wants to change seats for the adjacent students.

Can you write a SQL query to output the result for Mary?

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Abbot   |   
|    2    | Doris   |
|    3    | Emerson |
|    4    | Green   |
|    5    | Jeames  |
+---------+---------+

For the sample input, the output is:

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Doris   |
|    2    | Abbot   |
|    3    | Green   |
|    4    | Emerson |
|    5    | Jeames  |
+---------+---------+

Note:

If the number of students is odd, there is no need to change the last one’s seat.

SQL Schema

Create table If Not Exists seat(id int, student varchar(255))
Truncate table seat
insert into seat (id, student) values ('1', 'Abbot')
insert into seat (id, student) values ('2', 'Doris')
insert into seat (id, student) values ('3', 'Emerson')
insert into seat (id, student) values ('4', 'Green')
insert into seat (id, student) values ('5', 'Jeames')

My Interesting Code

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

My Perspective

This question is simple, and you just use “case”, like “if…else”, to accomplish this question.

However, I had a problem when doing this question. Initially, I used “id != max(id)” to judge, and the result was null because this method means no selection. Finally, I use current phrase to use “max(id)” is ok.

Also, there is a useful link, and I think it can help you. (chinese version)