SQL multi-Conditional ordering

A multi-conditional sort can be implemented by using a case and then conditional statement after the order by statement.

Select *  from ORDER  by  Case  when  Then 0 Else 1 End

Example:

1. Create a table Case_test

There are id,case_type,case_location,case_way of four fields.

2. Import data:

INSERT  into"Xiangzh". " Case_test "VALUES('1','theft Case','Taidong','Technology Unlock');INSERT  into"Xiangzh". " Case_test "VALUES(' the','murder Case','Taidong','Technology Unlock');INSERT  into"Xiangzh". " Case_test "VALUES(' About','theft Case','Jiangxi Road','Technology Unlock');INSERT  into"Xiangzh". " Case_test "VALUES('5','theft Case','Taidong','Violent Unlocking');INSERT  into"Xiangzh". " Case_test "VALUES('6','theft Case','Jiangxi Road','Violent Unlocking');INSERT  into"Xiangzh". " Case_test "VALUES('7','murder Case','Taidong','Violent Unlocking');INSERT  into"Xiangzh". " Case_test "VALUES('8','murder Case','Jiangxi Road','Technology Unlock');INSERT  into"Xiangzh". " Case_test "VALUES('9','murder Case','Jiangxi Road','Violent Unlocking');INSERT  into"Xiangzh". " Case_test "VALUES('Ten','theft Case','Taidong','Technology Unlock');

Not sorted:

3. Multi-conditional grouping sorting

Select *  from"Case_test"ORDER  by  Case  when"Case_type"='theft Case'  Then 0 Else 1 End, Case  when"Case_location"= 'Taidong'  Then 0 Else 1 End, Case  when"Case_way"= 'Technology Unlock'  Then 0 Else 1 End ASC

The query results are sorted by conditional grouping:

4. Sort the number of conditions

Select *  from"Case_test"ORDER  by  Case  when"Case_type"='theft Case'  and"Case_location"= 'Taidong'  and"Case_way"= 'Technology Unlock'  Then 0 when"Case_type"='theft Case'  and"Case_location"= 'Taidong'  Then 1 when"Case_type"='theft Case'  and"Case_way"= 'Technology Unlock'  Then 2 when"Case_location"= 'Taidong'  and"Case_way"= 'Technology Unlock'  Then 3 when"Case_type"='theft Case'  Then 4 when"Case_location"= 'Taidong'  Then 5 when"Case_way"= 'Technology Unlock'  Then 6Else 7End

The query results are sorted by the number of criteria that are met:

If there is a more reasonable SQL writing, please leave a message to discuss.

Reference:

Examples of Oracle conditionally ordered

How Oracle Sorts by criteria

SQL multi-Conditional ordering