If you want to sort the list by a certain order, such as a list of numbers from the beginning of 4,5,1, and then in ascending order, you can create a sort order by IF statement rule with additional columns for sorting:<\/p>\n
\r\nSELECT id,\r\n-- additional columns for sorting\r\nIF(id=4, 1, 0) c1, IF(id=5, 1, 0) c2, IF(id=1, 1, 0) c3 \r\nFROM test \r\nWHERE id IN(1,2,3,4,5,6,7,8,9,10)\r\n-- now sort by additional columns descending and other ascending\r\nORDER BY c1 DESC, c2 DESC, c3 DESC, id ASC\r\n<\/pre>\nQuery result:<\/p>\n
\r\nid\tc1\tc2\tc3\r\n4\t1\t0\t0\r\n5\t0\t1\t0\r\n1\t0\t0\t1\r\n6\t0\t0\t0\r\n7\t0\t0\t0\r\n8\t0\t0\t0\r\n9\t0\t0\t0\r\n10\t0\t0\t0\r\n<\/pre>\nIn this example, the statement condition returns 1 or 0, since 0 is above 1, so do a reverse sort by descending order, then the values \u200b\u200bin additional columns will be first. MySQL statements can be used immediately in the sorting, then the query is changed as follows:<\/p>\n
\r\nSELECT id \r\nFROM test \r\nWHERE id IN(1,2,3,4,5,6,7,8,9,10)\r\n-- sort order by conditions\r\nORDER BY IF(id=4, 1, 0) DESC, IF(id=5, 1, 0) DESC, IF(id=1, 1, 0) DESC, id ASC\r\n<\/pre>\nQuery result:<\/p>\n
\r\nid\r\n4\r\n5\r\n1\r\n6\r\n7\r\n8\r\n9\r\n10\r\n<\/pre>\nSince the IF statement return 0 or 1, it can be replaced by equal operator, so query would be:<\/p>\n
\r\nSELECT id \r\nFROM test \r\nWHERE id IN(1,2,3,4,5,6,7,8,9,10)\r\n-- sort order by equal operators\r\nORDER BY id=4 DESC, id=5 DESC, id=1 DESC, id ASC\r\n<\/pre>\nQuery result:<\/p>\n
\r\nid\r\n4\r\n5\r\n1\r\n6\r\n7\r\n8\r\n9\r\n10\r\n<\/pre>\nInstead IF statement can use CASE statement:<\/p>\n
\r\nSELECT id\r\nFROM test \r\nWHERE id IN(1,2,3,4,5,6,7,8,9,10)\r\nORDER BY \r\n-- sort by case\r\nCASE id\r\n WHEN 4 THEN 1\r\n WHEN 5 THEN 2\r\n WHEN 1 THEN 3\r\n ELSE 4\r\nEND ASC\r\n<\/pre>\nQuery result:<\/p>\n
\r\nid\r\n4\r\n5\r\n1\r\n6\r\n7\r\n8\r\n9\r\n10\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"If you want to sort the list by a certain order, such as a list of numbers from the beginning of 4,5,1, and then in ascending order, you can create a sort order by IF … Continue reading