626. Exchange Seats

Description

SQL Schema

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.

My Solution

Source Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SELECT 
    CASE
        --final row and odd; do nothing
        WHEN s.id % 2 = 1 AND s.id = (SELECT COUNT(id) FROM seat)
            THEN s.id
        --odd row; swap with next row
        WHEN s.id % 2 = 1
            THEN s.id + 1
        --even row; swap with previous
        WHEN s.id % 2 = 0
            THEN s.id - 1
    END
AS id, s.student
FROM seat AS s
ORDER BY id

Analysis

I had to think about this one for awhile but actually writing the solution was easy.

My first thought was to join the table with itself and look at each possible pair of students and swap the ones with conscutive ID numbers. But then I realized that's unnecessary. You can just directly change the ID numbers. That's it. Odd numbered IDs get their ID increased by one, even number IDs get them descreased by 1. If you have an odd number of students, you can't increase their ID since that would mean you have an extra student out of nowhere. So instead we just leave it alone.