Order Two Columns Independently - Practice Coding Problems

Order Two Columns Independently - Problem

Database Medium

You have a database table called Data with two integer columns: first_col and second_col. Your task is to create a query that independently sorts these columns in different orders.

Goal: Create a result set where:

first_col values are arranged in ascending order
second_col values are arranged in descending order

The key insight is that these columns should be sorted independently - meaning the first value in the sorted first_col doesn't necessarily come from the same row as the first value in the sorted second_col.

Table Schema:

+-------------+------+
| Column Name | Type |
+-------------+------+
| first_col   | int  |
| second_col  | int  |
+-------------+------+

This problem tests your understanding of window functions and row numbering in SQL, which are essential for data analysis and reporting tasks.

Input & Output

example_1.sql — Basic Case

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

› Output: +------------+-------------+ | first_col | second_col | +------------+-------------+ | 1 | 4 | | 2 | 3 | | 3 | 1 | +------------+-------------+

💡 Note: first_col is sorted ascending: [1,2,3]. second_col is sorted descending: [4,3,1]. The result combines them by position.

example_2.sql — With Duplicates

$ Input: Data table: +------------+-------------+ | first_col | second_col | +------------+-------------+ | 1 | 2 | | 1 | 2 | | 2 | 1 | +------------+-------------+

› Output: +------------+-------------+ | first_col | second_col | +------------+-------------+ | 1 | 2 | | 1 | 2 | | 2 | 1 | +------------+-------------+

💡 Note: first_col ascending: [1,1,2]. second_col descending: [2,2,1]. Duplicates maintain their relative positions.

example_3.sql — Single Row

$ Input: Data table: +------------+-------------+ | first_col | second_col | +------------+-------------+ | 5 | 10 | +------------+-------------+

› Output: +------------+-------------+ | first_col | second_col | +------------+-------------+ | 5 | 10 | +------------+-------------+

💡 Note: With only one row, the result remains unchanged regardless of sorting order.

Visualization

Tap to expand

Understanding the Visualization

Identify the columns

We have first_col (to be sorted ascending) and second_col (to be sorted descending)

Sort independently

Use ROW_NUMBER() window function to assign positions to each column when sorted

Join by position

Combine the sorted columns using their row number positions

Return result

The final result has first_col in ascending order and second_col in descending order

Key Takeaway

🎯 Key Insight: Window functions with ROW_NUMBER() allow us to sort columns independently while maintaining the ability to recombine them by position

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Dominated by sorting operations for each column

⚡ Linearithmic

Space Complexity

O(n)

Space for storing intermediate results and row numbers

⚡ Linearithmic Space

Constraints

1 ≤ number of rows ≤ 1000
-10⁶ ≤ first_col, second_col ≤ 10⁶
The table may contain duplicate values
Both columns must be sorted independently

Asked in

a Amazon 35 ⊞ Microsoft 28 G Google 22 f Meta 18

The optimal approach uses window functions with ROW_NUMBER() to independently sort each column and assign position numbers. By creating separate CTEs for each sorted column and joining them on their row numbers, we achieve O(n log n) time complexity with clean, readable SQL code.

Common Approaches

Approach	Time	Space	Notes
✓ Basic Subquery Approach	O(n²)	O(n)	Use separate subqueries for each column with LIMIT and OFFSET
Window Functions with ROW_NUMBER (Optimal)	O(n log n)	O(n)	Use ROW_NUMBER() window function to assign positions to sorted values, then join

Basic Subquery Approach — Algorithm Steps

Generate row numbers from 1 to total row count
For each position n, find the nth smallest value in first_col using LIMIT OFFSET
For each position n, find the nth largest value in second_col using LIMIT OFFSET
Combine the results

Visualization

Tap to expand

Step-by-Step Walkthrough

Generate positions

Create row numbers 1, 2, 3, ...

Scan for first_col

For each position, scan entire table to find nth smallest in first_col

Scan for second_col

For each position, scan entire table to find nth largest in second_col

Code -

solution.c — C

#include <stdio.h>
#include <string.h>

void solution() {
    const char* query = ""
    "WITH positions AS (\n"
    "    SELECT \n"
    "        ROW_NUMBER() OVER () as pos\n"
    "    FROM Data\n"
    ")\n"
    "SELECT \n"
    "    (\n"
    "        SELECT first_col \n"
    "        FROM Data \n"
    "        ORDER BY first_col ASC \n"
    "        LIMIT 1 OFFSET (p.pos - 1)\n"
    "    ) as first_col,\n"
    "    (\n"
    "        SELECT second_col \n"
    "        FROM Data \n"
    "        ORDER BY second_col DESC \n"
    "        LIMIT 1 OFFSET (p.pos - 1)\n"
    "    ) as second_col\n"
    "FROM positions p\n"
    "ORDER BY p.pos;\n";
    
    printf("%s\n", query);
}

int main() {
    solution();
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

For each of n rows, we scan the table to find the nth element

⚠ Quadratic Growth

Space Complexity

O(n)

Space needed for intermediate results and sorting

⚡ Linearithmic Space

Constraints

1 ≤ number of rows ≤ 1000
-10⁶ ≤ first_col, second_col ≤ 10⁶
The table may contain duplicate values
Both columns must be sorted independently

38.2K Views

Medium Frequency

~15 min Avg. Time

1.5K 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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Basic Subquery Approach — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler