{"id":6503,"date":"2026-06-28T10:12:38","date_gmt":"2026-06-28T01:12:38","guid":{"rendered":"https:\/\/route-zero.com\/recruit\/?post_type=route&p=6503"},"modified":"2026-10-28T10:12:49","modified_gmt":"2026-10-28T01:12:49","slug":"sql-order-by-case-custom-sort","status":"publish","type":"route","link":"https:\/\/route-zero.com\/recruit\/route\/6503\/","title":{"rendered":"SQL\u306eORDER BY CASE\u3067\u72ec\u81ea\u9806\u306b\u4e26\u3079\u308b"},"content":{"rendered":"

SQL\u3067\u300c\u7dca\u6025\u2192\u5bfe\u5fdc\u4e2d\u2192\u672a\u7740\u624b\u2192\u5b8c\u4e86\u300d\u306e\u3088\u3046\u306a\u72ec\u81ea\u306e\u9806\u756a\u306b\u4e26\u3079\u305f\u3044\u3068\u304d\u306f\u3001`ORDER BY CASE` \u3067\u5404\u30b9\u30c6\u30fc\u30bf\u30b9\u3092\u6570\u5024\u306e\u4e26\u3073\u9806\u306b\u5909\u63db\u3057\u307e\u3059\u3002NULL\u3084\u540c\u3058\u512a\u5148\u5ea6\u306e\u884c\u306b\u3082\u9806\u5e8f\u3092\u6c7a\u3081\u308b<\/strong>\u3068\u3001\u753b\u9762\u3084\u5e33\u7968\u306e\u7d50\u679c\u304c\u3076\u308c\u306b\u304f\u304f\u306a\u308a\u307e\u3059\u3002<\/p>\n

\u7d50\u8ad6\uff1aORDER BY CASE\u306b\u72ec\u81ea\u306e\u9806\u4f4d\u3092\u8fd4\u3059<\/h2>\n

\u6587\u5b57\u5217\u306e `ORDER BY status` \u306f\u901a\u5e38\u3001\u6307\u5b9a\u3057\u305f\u696d\u52d9\u4e0a\u306e\u512a\u5148\u9806\u4f4d\u3067\u306f\u306a\u304f\u6587\u5b57\u5217\u306e\u7167\u5408\u898f\u5247\u306b\u5f93\u3063\u3066\u4e26\u3073\u307e\u3059\u3002\u6c42\u3081\u308b\u9806\u5e8f\u3092\u81ea\u5206\u3067\u6c7a\u3081\u308b\u306a\u3089\u3001`CASE` \u3067\u6570\u5024\u3092\u5272\u308a\u5f53\u3066\u3001\u305d\u306e\u6570\u5024\u3092\u6607\u9806\u306b\u4e26\u3079\u307e\u3059\u3002<\/p>\n

SELECT id, status\r\nFROM tasks\r\nORDER BY CASE status\r\n    WHEN 'urgent'      THEN 0\r\n    WHEN 'in_progress' THEN 1\r\n    WHEN 'todo'        THEN 2\r\n    WHEN 'done'        THEN 3\r\n    ELSE 9\r\nEND ASC, id ASC;<\/code><\/pre>\n
`ELSE 9` \u306f\u5b9a\u7fa9\u3057\u3066\u3044\u306a\u3044\u30b9\u30c6\u30fc\u30bf\u30b9\u3092\u5f8c\u308d\u306b\u7f6e\u304f\u305f\u3081\u306e\u4fdd\u967a\u3067\u3059\u3002`status` \u304cNULL\u306e\u884c\u3082 `ELSE` \u306b\u5165\u308a\u307e\u3059\u3002`id ASC` \u306f\u540c\u3058\u30b9\u30c6\u30fc\u30bf\u30b9\u306e\u884c\u306e\u9806\u756a\u3092\u5b89\u5b9a\u3055\u305b\u308b\u305f\u3081\u306b\u5165\u308c\u3066\u3044\u307e\u3059\u3002<\/p>\n
\n
\u30b5\u30f3\u30d7\u30eb\u30c7\u30fc\u30bf\u3068\u671f\u5f85\u3059\u308b\u51fa\u529b\u9806<\/h2>\n
\u4ee5\u4e0b\u306f `id` \u304c\u4e00\u610f\u3067\u3001`status` \u3068 `priority` \u3092\u6301\u3064\u5c0f\u3055\u306a `tasks` \u30c6\u30fc\u30d6\u30eb\u3067\u3059\u3002DB\u306b\u65e2\u5b58\u306e\u540c\u540d\u30c6\u30fc\u30d6\u30eb\u304c\u3042\u308b\u74b0\u5883\u3067\u306f\u305d\u306e\u307e\u307e\u5b9f\u884c\u305b\u305a\u3001\u691c\u8a3c\u7528DB\u3067\u8a66\u3057\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n
CREATE TABLE tasks (\r\n    id INTEGER PRIMARY KEY,\r\n    status VARCHAR(20),\r\n    priority INTEGER\r\n);\r\n\r\nINSERT INTO tasks (id, status, priority) VALUES\r\n    (11, 'urgent', 5),\r\n    (12, 'urgent', NULL),\r\n    (13, 'in_progress', 2),\r\n    (14, 'todo', 1),\r\n    (15, 'done', 3),\r\n    (16, NULL, NULL);<\/code><\/pre>\n\n\n\n\n\n\n\n\n\n\n
\u671f\u5f85\u3059\u308b\u4e26\u3073<\/th>\nid<\/th>\nstatus<\/th>\npriority<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n11<\/td>\nurgent<\/td>\n5<\/td>\n<\/tr>\n
2<\/td>\n12<\/td>\nurgent<\/td>\nNULL<\/td>\n<\/tr>\n
3<\/td>\n13<\/td>\nin_progress<\/td>\n2<\/td>\n<\/tr>\n
4<\/td>\n14<\/td>\ntodo<\/td>\n1<\/td>\n<\/tr>\n
5<\/td>\n15<\/td>\ndone<\/td>\n3<\/td>\n<\/tr>\n
6<\/td>\n16<\/td>\nNULL<\/td>\nNULL<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\u300c\u7dca\u6025\u300d\u3092\u6700\u521d\u306b\u3057\u3001\u30b9\u30c6\u30fc\u30bf\u30b9\u304c\u5b9a\u7fa9\u3055\u308c\u3066\u3044\u306a\u3044\u884c\u306f\u6700\u5f8c\u3078\u3002\u3053\u306e\u3088\u3046\u306b\u671f\u5f85\u3059\u308b\u884cID\u306e\u9806\u756a<\/strong>\u3092\u6c7a\u3081\u3066\u304b\u3089SQL\u3092\u66f8\u304f\u3068\u3001\u30b3\u30fc\u30c9\u30ec\u30d3\u30e5\u30fc\u3067\u4ed5\u69d8\u3092\u7167\u5408\u3057\u3084\u3059\u304f\u306a\u308a\u307e\u3059\u3002<\/p>\n
\n
\u512a\u5148\u5ea6\u3068NULL\u3092\u7d44\u307f\u5408\u308f\u305b\u305f\u5b9f\u7528SQL<\/h2>\n
\u30b9\u30c6\u30fc\u30bf\u30b9\u304c\u540c\u3058\u3068\u304d\u3001`priority` \u306e\u5927\u304d\u3044\u5024\u3092\u5148\u306b\u51fa\u3057\u305f\u3044\u5834\u5408\u3092\u8003\u3048\u307e\u3059\u3002NULL\u512a\u5148\u5ea6\u306f\u5f8c\u308d\u306b\u56de\u3057\u3001\u6700\u5f8c\u306b\u4e00\u610f\u306aid\u3067\u9806\u5e8f\u3092\u78ba\u5b9a\u3055\u305b\u307e\u3059\u3002<\/p>\n
SELECT id, status, priority\r\nFROM tasks\r\nORDER BY\r\n    CASE status\r\n        WHEN 'urgent'      THEN 0\r\n        WHEN 'in_progress' THEN 1\r\n        WHEN 'todo'        THEN 2\r\n        WHEN 'done'        THEN 3\r\n        ELSE 9\r\n    END ASC,\r\n    CASE WHEN priority IS NULL THEN 1 ELSE 0 END ASC,\r\n    priority DESC,\r\n    id ASC;<\/code><\/pre>\n
\u30dd\u30a4\u30f3\u30c8\u306f\u300c\u30b9\u30c6\u30fc\u30bf\u30b9\u9806\u4f4d \u2192 NULL\u6709\u7121 \u2192 \u6570\u5024\u512a\u5148\u5ea6 \u2192 \u4e00\u610f\u30ad\u30fc\u300d\u3068\u3044\u3046\u9806\u3067\u3059\u3002\u5f8c\u308d\u306e\u4e26\u3073\u66ff\u3048\u5f0f\u306f\u3001\u305d\u308c\u307e\u3067\u306e\u5f0f\u3067\u540c\u9806\u4f4d\u306b\u306a\u3063\u305f\u884c\u3060\u3051\u306b\u5f71\u97ff\u3057\u307e\u3059\u3002<\/p>\n
\n
\u7528\u8a9e\u89e3\u8aac\uff1aNULLS FIRST \/ NULLS LAST<\/strong>
PostgreSQL\u306a\u3069\u3067NULL\u3092\u5148\u982d\u30fb\u672b\u5c3e\u306e\u3069\u3061\u3089\u3078\u7f6e\u304f\u304b\u6307\u5b9a\u3059\u308b\u69cb\u6587\u3067\u3059\u3002\u5bfe\u5fdc\u3059\u308bDB\u3084\u65e2\u5b9a\u306e\u9806\u5e8f\u304c\u7570\u306a\u308b\u305f\u3081\u3001\u8907\u6570DB\u3067\u4f7f\u3046SQL\u3067\u306fCASE\u306b\u3088\u308b\u660e\u793a\u7684\u306aNULL\u5224\u5b9a\u3082\u9078\u629e\u80a2\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n<\/blockquote>\n
\n
\u3088\u304f\u3042\u308b\u9593\u9055\u3044\uff1a\u671f\u5f85\u9806\u3092\u6587\u5b57\u5217\u9806\u306b\u4efb\u305b\u308b<\/h2>\n
\u6b21\u306e\u66f8\u304d\u65b9\u306f\u69cb\u6587\u30a8\u30e9\u30fc\u3067\u306f\u3042\u308a\u307e\u305b\u3093\u304c\u3001\u696d\u52d9\u306e\u512a\u5148\u5ea6\u3092\u8868\u3057\u3066\u3044\u307e\u305b\u3093\u3002\u65b0\u3057\u3044\u30b9\u30c6\u30fc\u30bf\u30b9\u3092\u8ffd\u52a0\u3057\u305f\u3068\u304d\u3001\u540d\u79f0\u306e\u8f9e\u66f8\u9806\u306b\u4f9d\u5b58\u3057\u3066\u60f3\u5b9a\u5916\u306e\u4f4d\u7f6e\u3078\u5165\u308b\u3053\u3068\u3082\u3042\u308a\u307e\u3059\u3002<\/p>\n
-- \u3053\u308c\u3067\u306f\u72ec\u81ea\u306e\u696d\u52d9\u512a\u5148\u9806\u306f\u4fdd\u8a3c\u3055\u308c\u306a\u3044\r\nSELECT id, status\r\nFROM tasks\r\nORDER BY status ASC;<\/code><\/pre>\n
`CASE` \u306e\u5206\u5c90\u6f0f\u308c\u3092\u9632\u3050\u305f\u3081\u3001\u6700\u5f8c\u306b\u306f\u5fc5\u305a `ELSE` \u3092\u7528\u610f\u3059\u308b\u3053\u3068\u3092\u304a\u3059\u3059\u3081\u3057\u307e\u3059\u3002\u5024\u306e\u9806\u5e8f\u304c\u300c0\u21921\u21922\u21923\u300d\u3067\u3042\u308b\u3053\u3068\u3082\u3001\u753b\u9762\u306e\u4ed5\u69d8\u66f8\u3068\u5408\u308f\u305b\u3066\u78ba\u8a8d\u3057\u307e\u3057\u3087\u3046\u3002<\/p>\n
\uff08CASE\u5f0f\u305d\u306e\u3082\u306e\u306e\u6761\u4ef6\u5206\u5c90\u304c\u5206\u304b\u3089\u306a\u3044\u5834\u5408\u306b\u3064\u3044\u3066\u306f\u300eSQL CASE\u5f0f\u306e\u4f7f\u3044\u65b9\uff5cSELECT\u30fbWHERE\u30fbUPDATE\u3067\u6761\u4ef6\u5206\u5c90<\/a>\u300f\u3092\u3054\u53c2\u7167\u304f\u3060\u3055\u3044\uff09<\/p>\n
DB\u3054\u3068\u306b\u5909\u308f\u308a\u3084\u3059\u3044NULL\u306e\u65e2\u5b9a\u9806\u5e8f<\/h2>\n
NULL\u306f\u666e\u901a\u306e\u6570\u5024\u3084\u6587\u5b57\u5217\u306e\u3088\u3046\u306b\u6bd4\u8f03\u3067\u304d\u307e\u305b\u3093\u3002`ORDER BY priority DESC` \u306eNULL\u914d\u7f6e\u3092DB\u306e\u65e2\u5b9a\u52d5\u4f5c\u3060\u3051\u306b\u4efb\u305b\u308b\u3068\u3001\u79fb\u690d\u5148\u3067\u898b\u3048\u65b9\u304c\u5909\u308f\u308b\u53ef\u80fd\u6027\u304c\u3042\u308a\u307e\u3059\u3002<\/p>\n\n\n\n\n\n\n\n
\u8981\u4ef6<\/th>\n\u66f8\u304d\u65b9<\/th>\n\u8003\u3048\u65b9<\/th>\n<\/tr>\n<\/thead>\n
\u3069\u306eDB\u3067\u3082NULL\u3092\u6700\u5f8c\u3078\u5bc4\u305b\u305f\u3044<\/td>\nCASE WHEN priority IS NULL THEN 1 ELSE 0 END<\/td>\n0\u3092\u5148\u30011\u3092\u5f8c\u3078<\/td>\n<\/tr>\n
PostgreSQL\u3067NULL\u3092\u6700\u5f8c\u3078<\/td>\npriority DESC NULLS LAST<\/td>\n\u5bfe\u5fdc\u3059\u308bDB\u306e\u69cb\u6587\u3092\u4f7f\u3046<\/td>\n<\/tr>\n
\u540c\u9806\u4f4d\u307e\u3067\u9806\u5e8f\u56fa\u5b9a<\/td>\nid ASC\u3092\u6700\u5f8c\u306b\u8ffd\u52a0<\/td>\n\u4e00\u610f\u306a\u5024\u3067\u4e26\u3073\u9806\u3092\u78ba\u5b9a<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
PostgreSQL\u306e\u516c\u5f0f\u8cc7\u6599\u3067\u306f `ORDER BY` \u306e\u5404\u5f0f\u306b `ASC` \/ `DESC` \u3084 `NULLS FIRST` \/ `NULLS LAST` \u3092\u6307\u5b9a\u3067\u304d\u307e\u3059\u3002\u79fb\u690d\u6027\u304c\u5fc5\u8981\u306a\u30b7\u30b9\u30c6\u30e0\u3067\u306f\u3001\u5229\u7528\u3057\u3066\u3044\u308bDB\u306e\u30c9\u30ad\u30e5\u30e1\u30f3\u30c8\u3092\u78ba\u8a8d\u3057\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n
WHERE\u30fbSELECT\u306eCASE\u3068\u306f\u4f55\u304c\u9055\u3046\u304b<\/h2>\n
`CASE` \u306f\u6761\u4ef6\u306b\u5fdc\u3058\u3066\u5024\u3092\u8fd4\u3059\u5f0f\u3067\u3059\u3002`SELECT` \u3067\u30e9\u30d9\u30eb\u3092\u4f5c\u308b\u3001`WHERE` \u3067\u6761\u4ef6\u5f0f\u306e\u4e00\u90e8\u306b\u3059\u308b\u3001`ORDER BY` \u3067\u4e26\u3073\u66ff\u3048\u30ad\u30fc\u3092\u4f5c\u308b\u3001\u3068\u3044\u3063\u305f\u4f7f\u3044\u5206\u3051\u304c\u3067\u304d\u307e\u3059\u3002<\/p>\n
\u4eca\u56de\u5fc5\u8981\u306a\u306e\u306f\u8868\u793a\u9806\u5e8f\u306e\u5236\u5fa1\u3060\u3051\u3067\u3059\u3002`CASE` \u3092 `SELECT` \u306b\u3082\u66f8\u3044\u3066\u9806\u4f4d\u5217\u3092\u8fd4\u3059\u5fc5\u8981\u306f\u3042\u308a\u307e\u305b\u3093\u3002\u30c7\u30d0\u30c3\u30b0\u6642\u306b\u9806\u4f4d\u3092\u53ef\u8996\u5316\u3057\u305f\u3044\u3068\u304d\u3060\u3051\u3001SELECT\u53e5\u3078\u540c\u3058\u5f0f\u3092\u8ffd\u52a0\u3059\u308b\u3068\u78ba\u8a8d\u3057\u3084\u3059\u304f\u306a\u308a\u307e\u3059\u3002<\/p>\n
\uff08SELECT\u6587\u3068ORDER BY\u306e\u57fa\u790e\u3092\u78ba\u8a8d\u3057\u305f\u3044\u5834\u5408\u306b\u3064\u3044\u3066\u306f\u300eSQL\u30b3\u30de\u30f3\u30c9\u4e00\u89a721\u9078<\/a>\u300f\u3092\u3054\u53c2\u7167\u304f\u3060\u3055\u3044\uff09<\/p>\n
\u91cd\u3044\u30af\u30a8\u30ea\u3067\u4f7f\u3046\u5834\u5408\u306e\u6ce8\u610f\u70b9<\/h2>\n
`ORDER BY CASE` \u306f\u5206\u304b\u308a\u3084\u3059\u3044\u4e00\u65b9\u3001\u4e26\u3079\u66ff\u3048\u7528\u306e\u5f0f\u304c\u8ffd\u52a0\u3055\u308c\u307e\u3059\u3002\u5927\u91cf\u306e\u7d50\u679c\u3092\u51e6\u7406\u3059\u308b\u5834\u5408\u3001\u65e2\u5b58\u306e\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u3060\u3051\u3067\u671f\u5f85\u3069\u304a\u308a\u306b\u9ad8\u901f\u5316\u3067\u304d\u308b\u3068\u306f\u9650\u308a\u307e\u305b\u3093\u3002<\/p>\n
    \n
  • `WHERE` \u3067\u5bfe\u8c61\u884c\u3092\u7d5e\u308c\u308b\u306a\u3089\u3001\u4e0d\u8981\u306a\u884c\u3092\u5148\u306b\u6e1b\u3089\u3059\u3002<\/li>\n
  • `EXPLAIN` \u306a\u3069\u3067\u30bd\u30fc\u30c8\u51e6\u7406\u3068\u5b9f\u884c\u8a08\u753b\u3092\u78ba\u8a8d\u3059\u308b\u3002<\/li>\n
  • \u512a\u5148\u5ea6\u306e\u696d\u52d9\u30eb\u30fc\u30eb\u304c\u56fa\u5b9a\u306a\u3089\u30de\u30b9\u30bf\u30fc\u30c6\u30fc\u30d6\u30eb\u7b49\u3078\u306e\u5206\u96e2\u3082\u691c\u8a0e\u3059\u308b\u3002<\/li>\n
  • \u65b0\u3057\u3044\u30b9\u30c6\u30fc\u30bf\u30b9\u304c\u5897\u3048\u308b\u904b\u7528\u3067\u306f `ELSE` \u306e\u6271\u3044\u3092\u4ed5\u69d8\u5316\u3059\u308b\u3002<\/li>\n<\/ul>\n
    \u6027\u80fd\u304c\u6c17\u306b\u306a\u308b\u3068\u304d\u306fSQL\u306e\u898b\u305f\u76ee\u3060\u3051\u3067\u5224\u65ad\u305b\u305a\u3001\u5b9f\u30c7\u30fc\u30bf\u91cf\u3067\u8a08\u6e2c\u3057\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n
    \u3088\u304f\u3042\u308b\u8cea\u554f<\/h2>\n
    CASE\u306b\u306a\u3044\u30b9\u30c6\u30fc\u30bf\u30b9\u306f\u3069\u3053\u3078\u4e26\u3073\u307e\u3059\u304b\uff1f<\/h3>\n
    \u3053\u306e\u30b5\u30f3\u30d7\u30eb\u3067\u306fELSE 9\u306a\u306e\u3067\u6700\u5f8c\u306e\u30b0\u30eb\u30fc\u30d7\u306b\u5165\u308a\u307e\u3059\u3002\u540c\u30b0\u30eb\u30fc\u30d7\u5185\u306f\u5f8c\u7d9a\u306e\u4e26\u3073\u66ff\u3048\u5f0f\u3067\u9806\u756a\u304c\u6c7a\u307e\u308a\u307e\u3059\u3002<\/p>\n
    ORDER BY\u3092\u4e8c\u3064\u4ee5\u4e0a\u66f8\u304f\u5fc5\u8981\u304c\u3042\u308a\u307e\u3059\u304b\uff1f<\/h3>\n
    ORDER BY\u53e5\u306f\u3072\u3068\u3064\u3067\u3001\u305d\u306e\u4e2d\u306b\u30ab\u30f3\u30de\u533a\u5207\u308a\u3067\u8907\u6570\u306e\u5f0f\u3092\u4e26\u3079\u307e\u3059\u3002\u5148\u306b\u6307\u5b9a\u3057\u305f\u5f0f\u306e\u9806\u4f4d\u304c\u512a\u5148\u3055\u308c\u307e\u3059\u3002<\/p>\n
    status\u304cNULL\u306e\u3068\u304d `WHEN NULL` \u3068\u66f8\u3051\u3070\u4e00\u81f4\u3057\u307e\u3059\u304b\uff1f<\/h3>\n
    \u901a\u5e38\u306eCASE status WHEN NULL\u3067\u306f\u4e00\u81f4\u3057\u307e\u305b\u3093\u3002NULL\u306fIS NULL\u3067\u5224\u5b9a\u3059\u308b\u304b\u3001\u4eca\u56de\u306e\u3088\u3046\u306bELSE\u3078\u542b\u3081\u308b\u8a2d\u8a08\u306b\u3057\u307e\u3059\u3002<\/p>\n
    \u307e\u305a6\u884c\u7a0b\u5ea6\u306e\u691c\u8a3c\u30c7\u30fc\u30bf\u3067\u671f\u5f85\u3059\u308bid\u306e\u4e26\u3073\u9806\u3092\u66f8\u304d\u51fa\u3057\u3001SQL\u306e\u7d50\u679c\u3068\u4e00\u81f4\u3059\u308b\u304b\u78ba\u8a8d\u3057\u3066\u307f\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n","protected":false},"featured_media":6504,"template":"","_links":{"self":[{"href":"https:\/\/route-zero.com\/recruit\/wp-json\/wp\/v2\/route\/6503"}],"collection":[{"href":"https:\/\/route-zero.com\/recruit\/wp-json\/wp\/v2\/route"}],"about":[{"href":"https:\/\/route-zero.com\/recruit\/wp-json\/wp\/v2\/types\/route"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/route-zero.com\/recruit\/wp-json\/wp\/v2\/media\/6504"}],"wp:attachment":[{"href":"https:\/\/route-zero.com\/recruit\/wp-json\/wp\/v2\/media?parent=6503"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}