Orderbyの条件に指定した項目に値が無い場合、別行の値を条件としたい（PostgreSQL)

Question

PostgreSQLについて、履歴データを保存しているテーブルからデータを抽出し、
履歴一覧を表示するシステムのSQL部分の改修を行っております。

履歴テーブル

履歴番号	登録日	修正元履歴番号
001	6/1	NULL
002	6/2	NULL
003	NULL	001
004	6/4	NULL

上記のようなデータベース（データの登録は別システムから登録されます）から登録日が新しい順にソートしたデータを抽出したいのですが、現状のSQLでは登録日がNULLの項目の場合最後尾の順番となってしまいます。

抽出結果

履歴番号	登録日	修正元履歴番号
004	6/4	NULL
002	6/2	NULL
001	6/1	NULL
003	NULL	001

そこで、修正元履歴番号をキーとして、修正元履歴番号と同じ履歴番号の登録日をソートの条件としたいと考えておりますが、PostgeSQLで実装は可能でしょうか

実現したいソート順イメージ

履歴番号	登録日	修正元履歴番号
004	6/4	NULL
002	6/2	NULL
003	NULL	001
001	6/1	NULL

イメージとしては履歴番号003の登録日を履歴番号001の登録日である6月1日であるものとしてソートしたいと考えております

現在のSQLは以下のようになっております

select *　FROM 履歴
order by 日付 desc,履歴番号 desc

当方でcase句で別名を付与し、サブクエリで抽出する方法も試行しましたが上手く行きませんでした

select 
履歴番号,
登録日,
修正元履歴番号,
      CASE
       WHEN 登録日　=　NULL THEN 登録日
       WHEN 登録日　=　NULL THEN (select 登録日　FROM 履歴　Where　履歴番号 = 修正元履歴番号）
      END AS ソート日付
FROM 履歴

当方PostgreSQLについて経験が浅く、皆様の知恵をお貸し頂けますと幸いです
※外部システムとの兼ね合い上テーブルの構造の変更が出来ない為、SQL文で実現できる方法がありましたらご教示頂けますと幸いです。

Comments

[Orlofsky]

質問のテーブル定義はCREATE TABLEに、テーブル中のデータはINSERTに、SELECT文も含めて https://teratail.com/help/question-tips#questionTips3-7 の [コード] で見易さを考えて修正できた方が適切なコメントが付き易いです。

[uraurabow]

ご指摘ありがとうございます。、SQLにおいて<コード＞を追加させて頂きました

Answer 1

postgres明るくないけど、「登録日 = NULL」は多分無理ゾ。
NULLはイコールで比較すると大概falseになるのでダメです。
あとselectした列はorderbyに指定できるのは覚えておいたほうがいいです。

select 
履歴番号,
登録日,
修正元履歴番号,
      CASE
       WHEN 登録日 IS NULL THEN (select 登録日 FROM 履歴 Where 履歴番号 = 修正元履歴番号）
       /*WHEN 登録日 IS NOT NULL THEN 登録日*/
       ELSE 登録日
      END AS ソート日付
FROM 履歴
ORDER BY ソート日付

一番ダメなのは全角スペースがあること…基本全角スペースはプログラムにおいては駆逐するべきものです。
全角スペースを表示するフォントもっと流行って。

Comments

[uraurabow]

お礼が遅くなり申し訳ございません。　回答ありがとうございました。
上記SQLを基に構築する事で事象を改善出来ました

Answer 2

select t1.*
FROM  履歴 t1 left join 履歴 t2
      on  t1.修正元履歴番号=t2.履歴番号
order by coalesce(t2.登録日, t1.登録日) desc, 履歴番号

Comments

[uraurabow]

お礼が遅くなり申し訳ございません。　回答ありがとうございました。
上記SQLを基に構築する事で事象を改善出来ました