626_Exchange Seats

Mary is a teacher in a middle school and she has a tableseatstoring 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.

Solution 1: change name

• Left join seat table twice to get the previous name and the next name for each name

• Use CASE WHEN to differentiate if we want to use the previous name or the next name

• Use MOD(id, 2) = 0 or 1 to differentiate if the id is odd or even

• Handle the last odd case if it exist with IFNULL() function

# Write your MySQL query statement below
SELECT s1.id,
    (
    CASE
        WHEN MOD(s1.id, 2) = 0 THEN s3.student
        WHEN MOD(s1.id, 2) = 1 THEN IFNULL(s2.student, s1.student)
    END
    ) AS student
FROM seat AS s1
    LEFT JOIN seat AS s2 ON s2.id - s1.id = 1
    LEFT JOIN seat AS s3 ON s1.id - s3.id = 1
ORDER BY s1.id ASC;

Solution 2: change id

For students with odd id, the new id is (id+1) after switch unless it is the last seat. And for students with even id, the new id is (id-1).

SELECT
    (CASE
        WHEN MOD(id, 2) != 0 AND counts != id THEN id + 1
        WHEN MOD(id, 2) != 0 AND counts = id THEN id
        ELSE id - 1
    END) AS id,
    student
FROM
    seat,
    (SELECT
        COUNT(*) AS counts
    FROM
        seat) AS seat_counts
ORDER BY id ASC;

Solution 3: using two UNION operators

/* get all the even numbered rows as odd numbered rows */
SELECT s1.id - 1 as id, s1.student
FROM Seat s1
WHERE MOD(s1.id, 2) = 0

UNION

/* get all the odd numbered rows as even numbered rows */
SELECT s2.id + 1 as id, s2.student
FROM Seat s2
WHERE MOD(s2.id, 2) = 1 AND s2.id != (SELECT MAX(id) FROM Seat)
/* Just don't get the last row as we will handle it in the next UNION */

UNION

/* get the last row if odd and don't change the id value */
SELECT s3.id, s3.student
FROM Seat s3
WHERE MOD(s3.id, 2) = 1 AND s3.id = (SELECT MAX(id) FROM Seat)

/* Order the result by id */
ORDER BY id ASC;