Skip to content

626. Exchange Seats

Description

Table: Seat

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| student     | varchar |
+-------------+---------+
id is the primary key (unique value) column for this table.
Each row of this table indicates the name and the ID of a student.
The ID sequence always starts from 1 and increments continuously.

 

Write a solution to swap the seat id of every two consecutive students. If the number of students is odd, the id of the last student is not swapped.

Return the result table ordered by id in ascending order.

The result format is in the following example.

 

Example 1:

Input: 
Seat table:
+----+---------+
| id | student |
+----+---------+
| 1  | Abbot   |
| 2  | Doris   |
| 3  | Emerson |
| 4  | Green   |
| 5  | Jeames  |
+----+---------+
Output: 
+----+---------+
| id | student |
+----+---------+
| 1  | Doris   |
| 2  | Abbot   |
| 3  | Green   |
| 4  | Emerson |
| 5  | Jeames  |
+----+---------+
Explanation: 
Note that if the number of students is odd, there is no need to change the last one's seat.

Solutions

Solution 1

Thinking

Adjacent odd/even seats swap, and a leftover last seat stays. A self-join can fetch the partner's name.

(id+1)^1-1 maps an odd id to the next even and an even id to the previous odd. COALESCE keeps the original name when the join misses.

1
2
3
4
5
6
# Write your MySQL query statement below
SELECT s1.id, COALESCE(s2.student, s1.student) AS student
FROM
    Seat AS s1
    LEFT JOIN Seat AS s2 ON (s1.id + 1) ^ 1 - 1 = s2.id
ORDER BY 1;

Solution 2

Thinking

Instead of joining, rewrite id: odd (not last) plus one, even minus one, last odd unchanged, then sort by the new id.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
# Write your MySQL query statement below
SELECT
    id + (
        CASE
            WHEN id % 2 = 1
            AND id != (SELECT MAX(id) FROM Seat) THEN 1
            WHEN id % 2 = 0 THEN -1
            ELSE 0
        END
    ) AS id,
    student
FROM Seat
ORDER BY 1;

Solution 3

Thinking

XOR-flipping the \(0\)-based index and taking RANK over that order swaps each pair in one expression; a leftover last row keeps its relative place.

1
2
3
4
5
# Write your MySQL query statement below
SELECT
    RANK() OVER (ORDER BY (id - 1) ^ 1) AS id,
    student
FROM Seat;

Solution 4

Thinking

Method 2 probes MAX(id) with a subquery. Comparing ROW_NUMBER() with a window COUNT detects the last row without a second scan.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
# Write your MySQL query statement below
SELECT
    CASE
        WHEN id & 1 = 0 THEN id - 1
        WHEN ROW_NUMBER() OVER (ORDER BY id) != COUNT(id) OVER () THEN id + 1
        ELSE id
    END AS id,
    student
FROM Seat
ORDER BY 1;

Comments