Formatted question description: https://leetcode.ca/all/626.html

626. Exchange Seats

Level

Medium

Description

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.

Solution

Change each row’s id, and sort the entries according to id in ascending order.

Use case statement to change the values of id. If id % 2 = 0, then change it to id - 1. Else, if id is the maximum id, then remain it unchanged. Else, change it to id + 1.

# Write your MySQL query statement below
select (
    case
        when id % 2 = 0 then id - 1
        when id = (select max(id) from seat) then id
        else id + 1
    end
) as id, student
    from seat
    order by id;

All Problems

All Solutions