2-3-24 逆ソート(リバースソート)

SQLパズル

order by ID desc,Val,Renban desc  の順序での順位付けと、
その逆順での順位付けを行う。

出力結果
ID  Val  Renban  Rank  RevRank
--  ---  ------  ----  -------
 4  700       2     1       22
 4  700       1     2       21
 4  800       1     3       20
 4  900       1     4       19
 3  600       2     5       18
 3  600       1     6       17
 2  500       2     7       16
 2  500       1     8       15
 1  100       5     9       14
 1  100       4    10       13
 1  100       3    11       12
 1  100       2    12       11
 1  100       1    13       10
 1  200       4    14        9
 1  200       3    15        8
 1  200       2    16        7
 1  200       1    17        6
 1  300       3    18        5
 1  300       2    19        4
 1  300       1    20        3
 1  400       2    21        2
 1  400       1    22        1

SQL

with WorkView as (select 1 as ID,100 as Val,1 as Renban from dual
union select 1,100,2 from dual
union select 1,100,3 from dual
union select 1,100,4 from dual
union select 1,100,5 from dual
union select 1,200,1 from dual
union select 1,200,2 from dual
union select 1,200,3 from dual
union select 1,200,4 from dual
union select 1,300,1 from dual
union select 1,300,2 from dual
union select 1,300,3 from dual
union select 1,400,1 from dual
union select 1,400,2 from dual
union select 2,500,1 from dual
union select 2,500,2 from dual
union select 3,600,1 from dual
union select 3,600,2 from dual
union select 4,700,1 from dual
union select 4,700,2 from dual
union select 4,800,1 from dual
union select 4,900,1 from dual)
select ID,Val,Renban,
Row_Number() over(order by ID desc,Val,Renban desc) as Rank,
Row_Number() over(order by ID asc ,Val desc,Renban asc) as RevRank
from WorkView
order by ID desc,Val,Renban desc;

解説

order by句の
ascとdescを入れ替えると、逆ソートができます。

例1 order by a,b,cの逆ソートは、
    order by a desc,b desc,c desc
例2 order by a desc,b,c ascの逆ソートは、
    order by a asc,b desc,c desc

---