2159. Order Two Columns Independently

SQL架构

Table: Data

+-------------+------+
| Column Name | Type |
+-------------+------+
| first_col   | int  |
| second_col  | int  |
+-------------+------+
There is no primary key for this table and it may contain duplicates.

Write an SQL query to independently:

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

The query 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          |
+-----------+------------+

with t1 as (select
first_col ,row_number() over(order by first_col )  ro
from
Data

),t2 as (
    select
    second_col,row_number() over(order by second_col desc) ro
    from
    Data

)
select
first_col,second_col
from
(
select
t1.first_col,t2.second_col,ro
from
t1 join t2
using(ro)
order by ro 
) s1