Objective

Write a SQL query to correct the swapped order items and return the proper pairing of order ID and item.

Table Schema:

• orders

Task Requirements:

• Adjacent order items have been swapped: order 1 has the item meant for order 2, and vice versa
• Reassign the correct order_id to each item by swapping adjacent pairs back
• Odd order_id rows should get order_id + 1, even rows should get order_id - 1
• If the total number of orders is odd, the last order should remain unchanged
• Sort the results by the corrected order ID in ascending order
  
  

Output columns: corrected_order_id, item

SELECT
    CASE
        WHEN order_id % 2 != 0 AND order_id = (SELECT MAX(order_id) FROM orders) THEN order_id
        WHEN order_id % 2 != 0 THEN order_id + 1
        ELSE order_id - 1
    END AS corrected_order_id,
    item
FROM orders
ORDER BY corrected_order_id;

Explanation

Step 1: Understanding the Swap Pattern

Adjacent orders have their items swapped. Order 1 holds the item for order 2, order 2 holds the item for order 1, and so on. To fix this, odd-positioned orders need to move forward by 1, and even-positioned orders need to move back by 1.

Step 2: Checking for the Last Odd Order

WHEN order_id % 2 != 0 AND order_id = (SELECT MAX(order_id) FROM orders) THEN order_id

This condition is checked first in the CASE expression. If the last order_id is odd, it has no partner to swap with, so it stays unchanged. The subquery SELECT MAX(order_id) FROM orders finds the highest order_id in the table. If the total count is even, this condition never triggers because the last order_id would be even.

Step 3: Swapping Odd Orders Up

WHEN order_id % 2 != 0 THEN order_id + 1

For all other odd order_ids (not the last), we add 1 to swap them forward. So order 1 becomes 2, order 3 becomes 4, and so on. The modulo operator % 2 != 0 identifies odd numbers.

Step 4: Swapping Even Orders Down

ELSE order_id - 1

The ELSE clause catches all even order_ids and subtracts 1. So order 2 becomes 1, order 4 becomes 3, etc. This completes the adjacent swap where each pair exchanges their positions.

Step 5: Sorting by Corrected ID

ORDER BY corrected_order_id

The output is sorted by corrected_order_id in ascending order, presenting the items in their proper sequence after the swap correction.