SQL Leetcode Question - Exchange Seats - How to Swap Values Between Two Consecutive Rows in SQL

Swapping values between consecutive rows in a SQL table can be tricky, especially when there’s an odd number of rows involved. In this post, we’ll explore how to swap the student names of consecutive student IDs in a table named seat. The special rule is that if the total count of students is odd, the last student’s seat remains the same.

This problem often appears in SQL interviews or coding challenges. You'll learn how to use a simple conditional approach to reorder the data for this swap without complicated joins or cursor manipulation.

   

Understanding the Problem: Swapping Student Seats by ID

Imagine you have a table with student names paired with their unique IDs in ascending order. Your task is to swap the names for every pair of consecutive IDs. For example:

ID	Student Name
1	Abbott
2	Brenda
3	Charlie
4	David
5	Ethan

After swapping every pair, the expected output should look like this:

ID	Student Name
1	Brenda
2	Abbott
3	David
4	Charlie
5	Ethan

Notice that IDs 1 & 2 swapped their student names, as did IDs 3 & 4. Since there are 5 rows (an odd number), the last student's seat (ID 5) is unchanged.

The key points are these:

• Swap values only between consecutive IDs
• If the number of rows is odd, the last row stays the same
• The swap always happens between an odd ID and the next even ID

This gives you a clear goal and sets the stage for the SQL logic.

Ordering the Data by ID

Before attempting any swaps, the data must be sorted properly. The sequence of IDs must be in ascending order because the swap logic depends on consecutive rows. If the order is off, swaps won't align correctly.

The input should look like this:

ID	Student Name
1	Abbott
2	Brenda
3	Charlie
4	David
5	Ethan

This ensures your swapping applies to pairs (1, 2), (3, 4), and so forth. Sorting by ID is a simple but crucial step to get the pairing correct.

Planning the Swap Logic

Now comes the heart of the solution: deciding when and how to swap the student names.

There are three conditions to consider regarding the ID:

1. If ID is odd and not the last row: Increase the ID by 1.
   • This means take the student name at an odd ID and assign it to the next even ID.
   • For example, ID 1 will map to ID 2.
2. If ID is odd and it is the last row: Keep the ID as is.
   • When the last row has an odd ID (like ID 5 in our example), it remains unchanged.
3. If ID is even: Decrease the ID by 1.
   • This assigns the student name at an even ID to the previous odd ID.
   • For example, ID 2 will map to ID 1.

The mathematical check for odd IDs uses the modulo operation:

• An ID is odd if ID % 2 != 0 (this means dividing by 2 leaves a remainder).

Here’s how these conditions work with our example:

• ID 1 (odd, not last) → swapped to ID 2
• ID 2 (even) → swapped to ID 1
• ID 3 (odd, not last) → swapped to ID 4
• ID 4 (even) → swapped to ID 3
• ID 5 (odd, last) → stays ID 5

This logic makes rewriting the student_name straightforward since each ID points at the “new ID” it will take the value from.

Implementing the Logic in SQL with CASE Statement

SQL’s CASE expression is perfect for this conditional assignment.

First, we must find the last ID in the table to handle the last row correctly. This is done using a subquery:

SELECT MAX(id) FROM seat

Next, we check if the ID is odd or even:

• MOD(id, 2) != 0 means the ID is odd.
• MOD(id, 2) = 0 means the ID is even.

Our SQL logic looks like this:

SELECT 
  CASE 
    WHEN MOD(id, 2) != 0 AND id != (SELECT MAX(id) FROM seat) THEN id + 1
    WHEN MOD(id, 2) != 0 AND id = (SELECT MAX(id) FROM seat) THEN id
    ELSE id - 1
  END AS new_id,
  student_name,
  id
FROM seat
ORDER BY id;

Here is what each part does:

• First WHEN: If id is odd and not the last row, new_id = id + 1.
• Second WHEN: If id is odd and is the last row, new_id = id (no change).
• ELSE: For even ids, new_id = id - 1.

To get the swapped student names, we join the result of this logic back on the seat table using the calculated new_id. Here's the complete query:

SELECT 
  s1.id,
  s2.student_name
FROM 
  seat s1
JOIN 
  seat s2 
ON 
  s2.id = CASE 
            WHEN MOD(s1.id, 2) != 0 AND s1.id != (SELECT MAX(id) FROM seat) THEN s1.id + 1
            WHEN MOD(s1.id, 2) != 0 AND s1.id = (SELECT MAX(id) FROM seat) THEN s1.id
            ELSE s1.id - 1
          END
ORDER BY 
  s1.id;

This query first determines the swapped ID for each row and then fetches the student name from the swapped ID.

Remember, structured indenting and clear formatting help make the SQL easy to read and maintain.

Running the Query and Observing the Results

Once you execute the query on the seat table, you get the student names swapped between consecutive IDs.

Expected output:

ID	Student Name
1	Brenda
2	Abbott
3	David
4	Charlie
5	Ethan

Notice:

• Students at IDs 1 and 2 swapped names.
• Students at IDs 3 and 4 swapped names.
• The last student at ID 5 kept their original name, since the total number of rows is odd.

This confirms that the conditional logic works well:

• Swaps happen only between consecutive IDs
• Odd last row stays unchanged

Using this approach, you can reliably swap any two consecutive rows in data that’s numbered and ordered properly.

Additional Tips and Best Practices

• Always sort your data by the key column when row order matters. Swapping logic depends heavily on this.
• Use subqueries carefully; repeated calls to SELECT MAX(id) could be optimized. In large data sets, consider using a variable or a CTE to store the max ID once.
• Comment your SQL code to explain complex logic. This helps others (or you in the future) understand your query at a glance.
• Practice similar interview questions that involve conditional logic with the CASE statement and row manipulation in SQL. It improves problem-solving and writing efficient queries.
• When testing, use small datasets to verify your swapping logic before applying it to large tables.

Swapping values between consecutive rows is not very common in day-to-day SQL but mastering it sharpens your understanding of row-based logic in SQL.

---

Swapping student seats by consecutive IDs shows how simple conditions can solve complex problems in SQL. It's a neat example of applying modulus checks, subqueries, and joins together to produce the desired result.