{"id":72,"date":"2008-11-19T15:12:28","date_gmt":"2008-11-19T13:12:28","guid":{"rendered":"http:\/\/dev.flauschig.ch\/wordpress\/?p=72"},"modified":"2009-08-21T08:59:51","modified_gmt":"2009-08-21T06:59:51","slug":"order-by-specific-order","status":"publish","type":"post","link":"http:\/\/dev.flauschig.ch\/wordpress\/?p=72","title":{"rendered":"“ORDER BY” specific order"},"content":{"rendered":"

Let’s say you have a Select Statement with a List of IDs in it’s “IN()” Part and want to get the Results in the same Order as the List of IDs.<\/p>\n

You now could use the same “ORDER BY” you used when you got the List of IDs but why reordering if you already got the order?<\/p>\n

Or you could reorder the result in your application.<\/p>\n

But there’s another solution to order it in SQL. Using the “CASE-WHEN-THEN” Statement. It may not be very efficient if you have many IDs but that’s the best I found.<\/p>\n

Example:<\/p>\n

\r\nSELECT * FROM MyTable\r\nWHERE ID IN (60898,306386,316781,345240,183559,106853,63373,57124,10260,328451)\r\nORDER BY (CASE\r\n    WHEN ID = 60898 THEN 1\r\n    WHEN ID = 306386 THEN 2\r\n    WHEN ID = 316781 THEN 3\r\n    WHEN ID = 345240 THEN 4\r\n    WHEN ID = 183559 THEN 5\r\n    WHEN ID = 106853 THEN 6\r\n    WHEN ID = 63373 THEN 7\r\n    WHEN ID = 57124 THEN 8\r\n    WHEN ID = 10260 THEN 9\r\n    WHEN ID = 328451 THEN 10\r\nEND)\r\n<\/pre>\n
To automate that in CSharp, it could look like:<\/p>\n
\r\nprivate string GenerateOrder(IList<int> idList)\r\n{\r\n    StringBuilder sb = new StringBuilder();\r\n    sb.Append(" ORDER BY (CASE");\r\n    for (int i=0; i<idList.Count; i++)\r\n    {\r\n        int headerId = idList[i];\r\n        sb.AppendFormat(" WHEN header_id = {0} THEN {1}", headerId, (i+1));\r\n    }\r\n    sb.Append(" END)");\r\n    return sb.ToString();\r\n}<\/pre>\n","protected":false},"excerpt":{"rendered":"
Let’s say you have a Select Statement with a List of IDs in it’s “IN()” Part and want to get the Results in the same Order as the List of IDs. You now could use the same “ORDER BY” you used when you got the List of IDs but why reordering if you already got … <\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[4,3,12],"tags":[44,43,42],"class_list":{"0":"entry","1":"post","2":"publish","3":"author-roemer","4":"has-more-link","5":"post-72","7":"format-standard","8":"category-csharp","9":"category-programming","10":"category-sql","11":"post_tag-generate","12":"post_tag-in","13":"post_tag-order"},"acf":[],"views":2339,"_links":{"self":[{"href":"http:\/\/dev.flauschig.ch\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/72"}],"collection":[{"href":"http:\/\/dev.flauschig.ch\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dev.flauschig.ch\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dev.flauschig.ch\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/dev.flauschig.ch\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=72"}],"version-history":[{"count":0,"href":"http:\/\/dev.flauschig.ch\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/72\/revisions"}],"wp:attachment":[{"href":"http:\/\/dev.flauschig.ch\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=72"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dev.flauschig.ch\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=72"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dev.flauschig.ch\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=72"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}