Exchange Seats - Problem

Given a Seat table with columns id and student, write a SQL solution to swap the seat id of every two consecutive students.

Rules:

  • 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 table structure is:

Seat table:

  • id (int): Primary key, starts from 1 and increments continuously
  • student (varchar): Name of the student

Table Schema

Seat
Column Name Type Description
id PK int Primary key, seat identifier starting from 1
student varchar Name of the student
Primary Key: id
Note: The ID sequence always starts from 1 and increments continuously

Input & Output

Example 1 — Basic Seat Swapping
Input Table:
id student
1 Alice
2 Bob
3 Charlie
4 David
5 Eve
Output:
id student
1 Bob
2 Alice
3 David
4 Charlie
5 Eve
💡 Note:

Students are swapped in pairs: (1,2) swap to become (Bob,Alice), (3,4) swap to become (David,Charlie). Student 5 (Eve) has no pair, so remains in the same position.
Example 2 — Even Number of Students
Input Table:
id student
1 Alice
2 Bob
Output:
id student
1 Bob
2 Alice
💡 Note:

With an even number of students, all students have pairs. Alice and Bob swap positions completely.
Example 3 — Single Student
Input Table:
id student
1 Alice
Output:
id student
1 Alice
💡 Note:

With only one student, there's no one to swap with, so Alice remains in seat 1.

Constraints

  • 1 ≤ id ≤ 50
  • student is a non-empty string
  • id is a continuous sequence starting from 1

Visualization

Tap to expand
Exchange Seats - SQL Solution INPUT: Seat Table id student 1 Alice 2 Bob 3 Carol 4 David 5 Eve Swap pairs: 1,2 3,4 5 Odd count: last stays ALGORITHM STEPS 1 Check if id is odd Use id % 2 = 1 2 For odd id: new_id = id + 1 (if not last) 3 For even id: new_id = id - 1 4 Handle last odd Keep same id if last SELECT CASE WHEN id%2=1 AND id=cnt THEN id WHEN id%2=1 THEN id+1 ELSE id-1 END ORDER BY id FINAL RESULT id student 1 Bob 2 Alice 3 David 4 Carol 5 Eve Swapped pairs: Alice Bob --> Carol David --> Eve OK - Seats exchanged! Last odd id unchanged Key Insight: Use CASE WHEN with modulo operator (%) to determine odd/even ids. For odd ids, add 1 (swap with next). For even ids, subtract 1 (swap with previous). Special handling: if odd id is the last row, keep it unchanged. TutorialsPoint - Exchange Seats | Optimal Solution using CASE WHEN

Related Problems

Asked in
Facebook 12 Microsoft 8
28.5K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen