"SQL" is sorted by specific required elements

In Oracle, we sort the usual order by column names in order, but sometimes we want some of the elements in this column to be in front or in the back or somewhere in the middle.

At this point we can use the order by case and then method.

For example: We want the leadership of this department to be in front of the name.

Select *  fromParterwhereLvl='leader' Order  by( Case  whenNameinch('Zhangsan','Lisi','Wangermazi') Then 0 Else 1 End)

The effect is: ' Zhangsan ', ' Lisi ', ' Wangermazi ' are sorted from beginning to end, and the remaining elements are randomly sorted after the three elements. Replace 1 and 0, and turn into ' Wangermazi ', ' Lisi ', ' Zhangsan ' in reverse order.

0 is the position of the element we need, and 1 is the position of the other element.

The same effect can also be achieved through the "decode" function

Select *  fromParterwhereLvl= 'leader' Order  byDecode (name,'Zhangsan',0,'Lisi',1,'Wangermazi',2,3)

The effect is the same as above, where 0,1,2 is the position of the three elements we need, and 3 is the location of the other elements.

But the first SQL statement has a problem, if the Name field is the only three elements, then when the condition is fully satisfied, so that the three elements are randomly sorted.

So if you use the case function, it's best to use this method

Select *  fromParterwhereLvl='leader' Order  by( Case  whenName='Zhangsan'  Then 0  whenName='Lisi'  Then 1Name='Wangermazi'  Then 2 Else 3 End)

So, even if the name field has only three elements, it can be sorted according to our needs.

It is important to note that only the three elements we need are sorted according to our defined order, the remaining elements are randomly ordered, and if we need the rest of the elements to be sorted in a field or reverse order, simply add the comma field name to the last face.

Select *  fromParterwhereLvl='leader' Order  by( Case  whenName='Zhangsan'  Then 0  whenName='Lisi'  Then 1Name='Wangermazi'  Then 2 Else 3 End), id DESC

Decode function is the same

Select *  fromParterwhereLvl= 'leader' Order  byDecode (name,'Zhangsan',0,'Lisi',1,'Wangermazi',2,3), id DESC

As we know from the above example, those numbers are the positions of the elements, and if we want to put the elements we need in the last place.

You just have to change the numbers.

Case function

Select *  fromParterwhereLvl='leader' Order  by( Case  whenName='Zhangsan'  Then 3  whenName='Lisi'  Then 2Name='Wangermazi'  Then 1 Else 0 End). IDdesc

Decode function is the same

Select *  fromParterwhereLvl= 'leader' Order  byDecode (name,'Zhangsan',3,'Lisi',2,'Wangermazi',1,0), IDdesc

The powerful decode function also allows us to sort the rest of the elements according to our own ideas.

If we're going to end up with ' Zhangsan ' and ' Lisi ', and put ' Wangermazi ' first, then we can use two decode functions simultaneously

Select *  fromParterwhereLvl= 'leader' Order  byDecode (name,'Zhangsan',3,'Lisi',2,0), Decode (name,'Wangermazi',1), IDdesc

So we realized the Zhang San and John Doe respectively ranked the penultimate and the penultimate, Wang er the first, the remaining elements in reverse order of the ID.

So we realized that the elements we needed were sorted in a particular order.

"SQL" is sorted by specific required elements