３つのテーブルを結合し、重複があった場合IDの大きいものを一件抽出する場合のSQLについて

Question

３つのテーブルを結合し、結果を取得しようとしておりますが、
該当結果が複数あった場合にIDが大きいものを
一件だけ抽出する処理の書き方がわからず困っております。

上記３つのテーブルを結合し、店コードと日付で検索を行い結果を取得したいです。

街テーブルからは緯度経度をもとに計算を行い結果をもとに結合を行っておりますが
緯度経度の性質上、隣り合った市区町村で緯度経度が被ってしまうことがあり、
もし緯度経度で複数の市区町村が抽出された場合は街コードが大きいものを取得したいと考えております。

---

試したこと
以下のsqlで店コードが指定のものかつ日付が指定の範囲でいちばん大きい結果を取得
することができましたが、meshcodeが複数該当してしまった場合に街コードが大きいものを一件
取り出す抽出方法がわかりませんでした。。。

SELECT stores.*,categories.category,cities.cityname
FROM(SELECT storecode,buildcode,lat.lng,date,rank() OVER (PARTITION BY storecode ORDER BY date DESC) AS rank
              FROM stores
　　　　　　　　 WHERE (storecode = 'A01' AND date <= '2021-04-19') 
              OR (storecode = 'B02' AND date <= '2021-04-20') 
              AS storetable
LEFT OUTER JOIN  categories ON stores.buildcode = categories.buildcode
LEFT OUTER JOIN  meshcode ON （latとlngを元に計算する式の為省略) = cities.meshcode
WHERE rank = 1

複数のテーブルを結合しつつ同時に複数条件で検索を行う際に
それぞれの該当結果が複数ある場合での結果をそれぞれ一件に絞り込む方法について
ご存知でしたらお伺いしたいです。

質問が長く見づらくなってしまい大変お手数をおかけしますが、どうぞよろしくお願い致します。
また、わかりにくい点や説明不足/不明点等ございましたらご指摘をお願い致します。

Comments

[sazi]

質問のSQLとテーブルが合っていません。
SQLにあるmeshcodeはcitiesの事ですか？
結合条件が省略されていますが、storesとの結合ですよね。

[sazi]

> 以下のsqlで店コードが指定のものかつ日付が指定の範囲でいちばん大きい結果を取得
することができました

「日付が指定の範囲でいちばん大きい結果」を求めている部分はどこですか？
rank()で順番は作り出していますが、限定する部分は見当たりませんが。

[sazi]

直接関係はないですけど、where条件は間違っていませんか？
WHERE storecode = 'A01' AND date <= '2021-04-19'
OR storecode = 'B02' AND date <= '2021-04-20'
ではなくて
WHERE (storecode = 'A01' AND date <= '2021-04-19')
OR (storecode = 'B02' AND date <= '2021-04-20')
だと思いますけど。

[yowayowanitohei]

sazi様
いつもご回答ありがとうございます。
失礼しました。。。最後の一文が抜けておりました。。。
条件に関してもご指摘の通りです。
where rank = 1

質問文修正させていただきます。

[sazi]

最初のコメントにも対応して下さい。

Answer 1

やりたいことは以下であっていますか?

• storesを何らかの条件で検索する。ただし、同じstore_idでdateが異なる行があった場合は、dateがより新しい行だけが欲しい。
• categoriesとcitiesのデータも一緒に欲しいが、citiesについては、meshcodeが同じ場合は、citycodeがより小さい行と紐づけたい

あっている場合、方法はいろいろあると思いますが、たとえば以下のようなクエリで実現できるかと思います。

SELECT *
FROM (
  SELECT *, RANK() OVER (PARTITION BY storecode ORDER BY date DESC) AS rnk
  FROM stores
  WHERE (storecode = 'A01' AND date <= '2021-04-19') 
  OR (storecode = 'B02' AND date <= '2021-04-20')
) AS s
LEFT OUTER JOIN categories ON s.buildcode = categories.buildcode
LEFT OUTER JOIN (
  SELECT *, RANK() OVER (PARTITION BY meshcode ORDER BY citycode) AS rnk
  FROM cities
) AS t ON （latとlngを元に計算する式の為省略) = t.meshcode
WHERE s.rnk = 1 AND t.rnk = 1

Comments

[yowayowanitohei]

neko_the_shadow様

いつもご回答ありがとうございます！
上記コードにて動作できました。。。！
SQLちゃんと書ける前にまず質問しっかりできるようになります（泣）
拙い質問からご忖度ありがとうございました。非常に助かりました

Answer 2

質問内容を忖度すると、こんな感じかと思います。

with target_stores as (
    SELECT *
    FROM  stores
    WHERE (storecode = 'A01' AND date <= '2021-04-19')
       OR (storecode = 'B02' AND date <= '2021-04-20')
)
select
from  target_stores 
      left join categories
      on    target_stores.buildcode = categories.buildcode
      left join meshcode
      on  （latとlngを元に計算する式の為省略) = meshcodes.meshcode
where not exists(
        select 1 from target_stores max_date 
        where max_date.storecode=target_stores.storecode 
          and max_date.date > target_stores.date
      )
  and not exists(
        select 1 from meshcode max_citycode 
        where max_citycode.meshcode=meshcode.meshcode 
          and max_citycode.citycode > meshcode.citycode
      )

Comments

[yowayowanitohei]

sazi様
わかりやすいご回答ありがとうございます。
追加で何度もご質問になってしまい大変申し訳ないのですが
max_dateとmax_citycodeについて意図した動作がよくわからず
確認させていただきたいです。。。
これはこのクエリとは別に設定が必要との認識でよろしいでしょうか。

[sazi]

> max_dateとmax_citycodeについて意図した動作がよくわからず
max_dateとmax_citycodeは相関副問合せで問合せするテーブル名が同じなので、別名を付けています。

[sazi]

相関副問合せで揃えましたが、ネストさせれば、meshcodeもrank()を使用すれば同じように出来ますね。

[yowayowanitohei]

理解に時間かかりましたが、動作できました！
質問抜け漏れないよう、しっかり記入するようにします泣
今後ともどうぞよろしくお願いします。