> For the complete documentation index, see [llms.txt](https://lei-d.gitbook.io/sql/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://lei-d.gitbook.io/sql/leetcode/626exchange-seats.md).

# 626\_Exchange Seats

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 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

```php
# 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).

```php
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

```php
/* 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;
```


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://lei-d.gitbook.io/sql/leetcode/626exchange-seats.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.