最新のIDから最初のIDを取得するSQL

Question

前提・実現したいこと

下記のようなテーブルを取り扱っています。
各商品の在庫のidと、商品のID、数量、次の在庫のIDを記載したテーブルになります。

|id|item_id|num|next_id|
|:--|:--:|--:|
|10001|0100|100|10005|
|10002|0100|100|10006|
|10003|0200|200|10007|
|10004|0300|300|10008|
|10005|0100|100|10009|
|10006|0100|100|10010|
|10009|0100|100|10011|
|10010|0100|100|10012|

最新のidから、一番最初のidを取得したいと考えています。

上記表ですと、10010のidを検索キーにして、id=1000110002を探し当てる方法を探しています。

SQL（OracleもしくはpostgreSQL）上で取得する方法はありますでしょうか？

よろしくおねがいいたします。

追記

解決できました！
初めての質問でしたが、
neko_the_shadowさん・yambejpさん、お時間割いていただき、ありがとうございました！
m.ts10806さん、ご指摘いただき、ありがとうございました！

Comments

[m.ts10806]

まずはご自身で思ったように組んでみては。
「あるか？」「可能か？」なら「あります」「可能です」という答えになります。

[退会済みユーザー]

ありがとうございます。
neko_the_shadowさんにご回答いただいた手法で実行できました。

Answer 1

以下のような再帰クエリで実現できると思います。

• OracleであればRECURSIVEキーワードを削除すれば動くと思います
• 閉路がある場合は、検出できずに無限ループとなって、結果が返ってこないので注意してください。

WITH RECURSIVE r (id, item_id, num, next_id, depth) AS (
    SELECT 
      t.id, 
      t.item_id, 
      t.num, 
      t.next_id, 
      0
    FROM t
    WHERE id = '10010'
  UNION ALL
    SELECT 
      t.id, 
      t.item_id, 
      t.num, 
      t.next_id, 
      depth + 1
    FROM t
    JOIN r ON t.next_id = r.id
)
SELECT
  r.id,
  r.item_id,
  r.num,
  r.next_id
FROM r
ORDER BY depth DESC
FETCH FIRST 1 ROWS ONLY;

Comments

[退会済みユーザー]

ご返事が大変遅くなり申し訳ございません。
ありがとうございます！！

いただいたSQLでやってみたらできました！
こんな方法があるのですね。今後も使っていきたいと思います！

Answer 2

select min(id) from tbl
where item_id=(
select item_id from tbl as t1
where not exists (select 1 from tbl where id>t1.id)
)

※最新idも自動取得でしたね・・・

Comments

[退会済みユーザー]

ありがとうございます！
説明が足りなくてすみません。

主キーがidになっていて、item_idが複数存在する可能性があります。
データベースの設計が悪いのか、主キーも存在しないので、next_idとidを無限につないでもとのidを探せないかを考えておりました。

何か対応の方法などありそうでしょうか…？

[yambejp]

失礼、かいて誤解にきづいて本文をなおしてしまいました
イメージ的にどうでしょう？

[退会済みユーザー]

ありがとうございます！
度々申し訳ございません。

質問が誤っておりました。
10010のidを検索キーにして、id=10002を探し当てる方法を探しておりました。

上記表だと、検索キーを10010にするなら、10002の結果がほしく、検索キーを10009にするなら、10001がほしいです。

ので、item_idとitem_idをつなげるのに加えて、next_idとidを無限につなげるようなイメージで算出できないか、、とイメージしておりました。

何度も申し訳ございません。

[yambejp]

なるほど、item_idは無視してよく、ずっとさかのぼっていくのですね？
そうなると最新id=10010にnext_idが存在していることは合理的ではありませんが・・

[退会済みユーザー]

ありがとうございます！

最初に書いていただいた条件のように、item_idが合致する条件は残しつつ、
on t1.item_id=t2.item_id

さかのぼっていければ、、と思っておりました。
わかりにくく申し訳ございません。

[yambejp]

どこまでもさかのぼっていくなら、以下のどれかでしょう
・データ構造を木構造に変えるか
・プロシージャを用意するか
・冗長になんどもleft joinするか

冗長パターンのsampleを上げておきますが、
これが先頭までほんとに行き着いているかはわかりません
心配なら何重にも保険をかけてjoinしてください

select least(
t1.id,
coalesce(t2.id,t1.id),
coalesce(t3.id,t1.id),
coalesce(t4.id,t1.id),
coalesce(t5.id,t1.id)
) as id from (
select * from tbl as t0
where not exists(select 1 from tbl where id>t0.id)
) as t1
left join tbl as t2 on t1.id=t2.next_id and t1.item_id=t2.item_id
left join tbl as t3 on t2.id=t3.next_id and t2.item_id=t3.item_id
left join tbl as t4 on t3.id=t4.next_id and t3.item_id=t4.item_id
left join tbl as t5 on t4.id=t5.next_id and t4.item_id=t5.item_id

[退会済みユーザー]

ありがとうございます！
冗長パターンをやるかって感じですよね。
プロシージャなども検討してみます。