Order Two Columns Independently - Problem

Given a table Data with two integer columns first_col and second_col, write a SQL query to independently sort the columns:

  • Sort first_col in ascending order
  • Sort second_col in descending order

The result should combine the sorted values by their row positions, not by their original relationships.

Table Schema

Data
Column Name Type Description
first_col int First integer column to be sorted ascending
second_col int Second integer column to be sorted descending
Note: This table may contain duplicate rows. No inherent relationship between column values.

Input & Output

Example 1 — Basic Independent Sorting
Input Table:
first_col second_col
1 4
3 2
2 3
Output:
first_col second_col
1 4
2 3
3 2
💡 Note:

The first_col is sorted in ascending order: [1, 2, 3]. The second_col is sorted in descending order: [4, 3, 2]. The results are combined by their new positions.
Example 2 — With Duplicate Values
Input Table:
first_col second_col
2 1
1 1
2 2
1 2
Output:
first_col second_col
1 2
1 2
2 1
2 1
💡 Note:

With duplicates, first_col ascending gives [1, 1, 2, 2] and second_col descending gives [2, 2, 1, 1]. The sorting maintains duplicate values while reordering independently.
Example 3 — Single Row
Input Table:
first_col second_col
5 10
Output:
first_col second_col
5 10
💡 Note:

With only one row, both columns remain unchanged as there's nothing to sort against.

Constraints

  • 1 ≤ n ≤ 1000 where n is the number of rows
  • first_col and second_col contain integers
  • The table may contain duplicate rows

Visualization

Tap to expand
Order Two Columns Independently INPUT first_col second_col 4 3 2 5 1 8 3 1 Table: Data Columns are independent No relationship between row values in output ALGORITHM STEPS 1 Add ROW_NUMBER() to each column sorted 2 Sort first_col ASC Ascending order 3 Sort second_col DESC Descending order 4 JOIN on row_num Combine by position SELECT a.first_col, b.second_col FROM (ROW_NUMBER ASC) a JOIN (ROW_NUMBER DESC) b ON a.rn = b.rn FINAL RESULT first_col second_col 1 8 2 5 3 3 4 1 ASC: 1,2,3,4 DESC: 8,5,3,1 OK Columns sorted independently! Joined by row position Key Insight: Use ROW_NUMBER() window function to assign sequential positions to each column after sorting. Then JOIN the two result sets on the row number to combine independently sorted columns by position. TutorialsPoint - Order Two Columns Independently | Optimal Solution

Related Problems

Asked in
Amazon 12 Microsoft 8 Google 6
23.4K 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