Order Two Columns Independently - Solution & Explanation

Problem Overview: You are given a table where the values in the first column must be sorted in ascending order and the values in the second column must be sorted in descending order. The key constraint is that both columns must be reordered independently while still producing a valid two‑column result table.

Approach 1: Window Function with ROW_NUMBER (O(n log n) time, O(n) space)

The cleanest solution uses SQL window functions. First, sort first_col in ascending order and assign a sequential index using ROW_NUMBER(). Then separately sort second_col in descending order and assign another ROW_NUMBER(). Because both sequences generate the same ordered index (1..n), you can join the two results on the row number. This effectively pairs the smallest value from the first column with the largest value from the second column, the second smallest with the second largest, and so on. Sorting dominates the cost, giving O(n log n) time, while the intermediate result sets require O(n) space.

Approach 2: CTE-Based Sorting and Join (O(n log n) time, O(n) space)

A more readable variation uses Common Table Expressions (CTEs). One CTE sorts the first column ascending and assigns row numbers. Another CTE sorts the second column descending with its own row numbers. The final query joins the two CTEs on the generated index. The algorithmic idea is identical to the window function approach, but the CTE structure makes the query easier to debug and reason about. Each CTE performs a sort operation, which keeps the complexity at O(n log n).

Approach 3: MySQL User Variables (O(n log n) time, O(1) extra space)

Older MySQL versions without window functions can simulate row numbering using session variables. Initialize a counter and increment it while selecting rows from a sorted query. Repeat the process for the second column with a separate variable and reverse ordering. Joining the two derived tables by the generated index produces the same final pairing. This approach avoids window functions but relies on MySQL-specific behavior, which makes it less portable across database systems.

Recommended for interviews: The ROW_NUMBER() window function approach is what interviewers usually expect. It demonstrates familiarity with database query design and modern SQL features such as SQL window functions. The CTE variant improves readability, while the variable-based technique shows how to handle environments without window functions.