SQL文で、ソート条件の場合分け

Question

登録日から30日以内の場合は、先頭にもってきて登録日降順

そうでない場合は、下に持ってきて「Number」カラムの値降順（Numberはint型の数値カラムです）

を実現したいです。

途中は省きますが、現状では

ORDER BY 
        CASE
            WHEN Created >= DATE_ADD(NOW(), INTERVAL -30 DAY) THEN 1（１）
            ELSE 2（２）
        END,
        Created DESC

と書いていて、これだと（１）も（２）も登録日降順で並びます。

（１）の場合は登録日降順
（２）の場合は「Number」カラム降順

とするには、どのようにSQLに追記すればいいのでしょうか。

よろしくお願いします。

Answer 1

ORDER BYでどうこうするのではなく、ORDER BYで制御しやすい値を生成してあげるのが通常の手法かと思います。
2案考えましたが、どちらも一長一短。

案1
同じCASEが2回登場するので冗長です。

SELECT
        CASE
            WHEN Created >= DATE_ADD(NOW(), INTERVAL -30 DAY) THEN 2
            ELSE 1
        END AS ORDER1,
        CASE
            WHEN Created >= DATE_ADD(NOW(), INTERVAL -30 DAY) THEN 30-DateDiff(NOW(), Created)
            ELSE Number
        END AS ORDER2,

ORDER BY 
        ORDER1 DESC, ORDER2 DESC

案2
案1を1カラムにまとめました。
仮に10000と20000にしています。
Numberデータが4桁より大きい場合は桁を増やしてください。
上限が決められないような場合、この案は使えません。

SELECT
        CASE
            WHEN Created >= DATE_ADD(NOW(), INTERVAL -30 DAY) THEN 20000 + (30 - DateDiff(NOW(), Created))
            ELSE 10000 + Number
        END AS ORDER1,
ORDER BY 
        ORDER1 DESC

Comments

[sazi]

日付型とint型で型が違うものをcaseの出力にするのは型の違いでエラーにならないですか？
仮に型を合わせるとしても、別体系になるように扱わないと正しくソートされないのでは？

[ttyp03]

型は合わせてるつもりですが。
時間がなかったので厳密に正しく書けてないかもしれません。
間違ってたらご指摘願います。

[sazi]

datediffで数値にしてるって事でしたか。
（暗黙変換はしてくれるんでしょうけどdatediffの戻型が今更ながら分からない・・・）
20000と10000でブロック分けしているのはあくまで例って事なんですね。

[ttyp03]

ブロック分けは例ではないですよ。
20000台に30日以内のレコード、10000台にそれ以外のレコードが来るように重み付けをしています。
20000台は20000～20030なのでいいですが、10000台はレコード数によっては19999を超える可能性があるので、桁を増やすなり別の対応が必要ですってことです。

Answer 2

登録日から30日以内の場合は、先頭にもってきて登録日降順、
そうでない場合は、下に持ってきて「Number」カラムの値降順（Numberはint型の数値カラムです）

上記は３つのソート項目に分解できます。

A.登録日から30日以内かどうか
B.登録日降順
C.「Number」カラムの値降順

ポイントとしては、Aの条件に該当しないBやCは同じ値にする事です。

order by
      CASE WHEN Created >= DATE_ADD(NOW(), INTERVAL -30 DAY) THEN 1 ELSE 2 end
    , CASE WHEN Created >= DATE_ADD(NOW(), INTERVAL -30 DAY) THEN Created end desc
    , CASE WHEN Created < DATE_ADD(NOW(), INTERVAL -30 DAY) THEN Number end

※上記の場合は値を与えない(=Null)としています。

Answer 3

mst10806様が仰ってるとおり、unionでやるのがわかりやすくていいと思います。
また、KCBR6502様の質問文をそのまま使うならば、この形でしょうか。

order by
  case
    when
      Created >= CURRENT_DATE - 30 then ((select min(Number) from table_name)-1)
    else
      Number
  end,
  Created

※なお、当方Postgresqlでの確認ですので、方言をMysqlに直してご利用下さい。

Answer 4

ざっくり回答で申し訳ないですが（テーブル定義やサンプルデータが不明でもありますし）、
「登録日から30日以内」のデータと「登録日から30日以降」のデータを別個で取得してUNIONするのが一番良さそうに思います。

Answer 5

今日が仮に2019-02-18だとして
※Numberを利用して結合データをソートするならzerofillしておくのがラク
（ただし想定する最大値を以上の桁を用意すること）

create table tbl (id int primary key,Number int(11) unsigned zerofill,Created date);
insert into tbl values
(1,10,'2018-01-01'),
(2,20,'2018-01-01'),
(3,15,'2019-01-01'),
(4,5,'2019-01-01'),
(5,1,'2019-02-01'),
(6,10,'2019-02-02'),
(7,10,'2019-02-01');

• とりあえず表示

select *,case when Created >= curdate() - interval 30 day then concat('1_',number)
else concat('2_',Created) end as odrder_data
from tbl;

• orderby

select * from tbl
order by case when Created >= curdate() - interval 30 day then concat('1_',number)
else concat('2_',Created) end desc;

Answer 6

• 登録日から30日以内の場合は登録日を返す
• 30日を超える場合は 1970-01-01 などの固定の値を返す

を CASE 式で出力して、降順ソート。
固定の値に関しては Number カラムで降順ソート。
とすれば良いのではないですか？