7-78 同じデータが連続しないようにソート

SQLパズル

TestTable
Val
---
  1
  1
  2
  2
  2
  3
  4
  4

同じデータが連続しないようにソートする。

出力結果
Val
---
  1
  2
  3
  4
  1
  2
  4
  2

SQL

--■■■データパターン1■■■
with TestTable as(select 1 as Val from dual
union all select 1 from dual
union all select 2 from dual
union all select 2 from dual
union all select 2 from dual
union all select 3 from dual
union all select 4 from dual
union all select 4 from dual)
select Val
  from TestTable
order by Row_Number() over(partition by Val order by 1),Val;

--■■■データパターン2■■■
with TestTable as (select 1 as Val from dual
union all select 1 from dual
union all select 1 from dual
union all select 1 from dual
union all select 2 from dual
union all select 2 from dual
union all select 2 from dual
union all select 2 from dual)
select Val
  from TestTable
order by Row_Number() over(partition by Val order by 1),Val;

解説

order by句で、分析関数のRow_Number関数を使ってます。

---