Skip to main content
Talentd

Order Two Columns Independently - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase3 min readAsked at: Booking.com
Practice this problem

Problem Statement

Table: Data

+-------------+------+
| Column Name | Type |
+-------------+------+
| first_col   | int  |
| second_col  | int  |
+-------------+------+
This table may contain duplicate rows.

 

Write a solution to independently:

  • order first_col in ascending order.
  • order second_col in descending order.

The result format is in the following example.

 

Example 1:

Input: 
Data table:
+-----------+------------+
| first_col | second_col |
+-----------+------------+
| 4         | 2          |
| 2         | 3          |
| 3         | 1          |
| 1         | 4          |
+-----------+------------+
Output: 
+-----------+------------+
| first_col | second_col |
+-----------+------------+
| 1         | 4          |
| 2         | 3          |
| 3         | 2          |
| 4         | 1          |
+-----------+------------+

Approach Overview

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.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Window Function with ROW_NUMBERO(n log n)O(n)Best modern SQL solution using window functions; clean and interview‑friendly
CTE Sorting + JoinO(n log n)O(n)When readability and step‑by‑step query structure are preferred
MySQL User VariablesO(n log n)O(1)Useful for older MySQL versions without window function support

Video Solution

LeetCode Medium 2159 Booking.com “Order 2 Columns Independently" Interview SQL Question Explanation • Everyday Data Science • 1,582 views views

Watch 1 more video solutions →

Frequently Asked Questions

Is Order Two Columns Independently easy or hard?
Order Two Columns Independently is categorized as Medium difficulty. The challenge is recognizing that both columns must be sorted separately and then aligned using generated row numbers, which requires familiarity with SQL window functions.
Order Two Columns Independently Python/Java solution
This problem is designed for SQL rather than general-purpose languages like Python or Java. The typical solution uses MySQL queries with ROW_NUMBER() and ORDER BY to independently sort columns and recombine them through a join.
How to solve Order Two Columns Independently in O(n)?
Achieving true O(n) time is not practical because the problem requires sorting values in two columns. Sorting-based solutions inherently take O(n log n). The optimal SQL approach sorts each column separately and aligns rows using ROW_NUMBER().
What is the best approach for Order Two Columns Independently?
The best approach uses SQL window functions with ROW_NUMBER(). Sort the first column in ascending order and the second column in descending order, assign row numbers to both sorted results, then join them by the generated index. This method is concise, readable, and works efficiently in modern MySQL and other SQL databases.
Is Order Two Columns Independently asked at Google/Amazon/Meta?
Database ordering and window function problems commonly appear in SQL interview rounds at companies like Amazon, Meta, and data-focused roles at Google. This problem specifically tests understanding of sorting, row numbering, and query composition using window functions.
What data structure is used in Order Two Columns Independently?
The problem primarily relies on SQL window functions rather than traditional data structures. ROW_NUMBER() generates sequential indices after sorting, which act as temporary keys used to join the two independently ordered result sets.
What is the time complexity of Order Two Columns Independently?
The dominant operation is sorting each column independently. Sorting takes O(n log n) time, and the final join by row number runs in O(n). Overall complexity is O(n log n) time with O(n) auxiliary space for intermediate query results.

Ready to solve this problem?

Practice Order Two Columns Independently with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary