Geek Answers Handbook

ORDER BY and = (equal) in MYSQL

I have a question that I came across an interesting piece of code. The MySQL statement has something like:

{...} ORDER BY any = 3 ASC, independently = 4 ASC, anything = 6 ASC {...}

Does this mean that he will place an order this way ?:

thing | whatever 15 | 1 1 | 3 5 | 3 8 | 3 2 | 4 3 | 4 6 | 4 4 | 6 7 | 6 9 | 6 14 | 9 21 | 10 18 | 9 ...

It seems that sorting only that which is equal to 3.4 and 6 generations is disordered ...

Is this the correct / supported syntax?

+8
sql mysql sql-order-by
source share
2 answers

whatever=3 evaluates to boolean with 0 as false and 1 as true .

Since it is ordered in ascending order, those that do not meet the condition are ordered first. Then the second ordering column whatever=4 comes into play, so those where whatever=4 will be the last in this group, etc.

For those where whatever not in (3,4,6) , the order is not specified to act as a tie-breaker, and the ordering is arbitrary.

MySQL syntax is supported, but not standard or portable.

+7
source share

In fact, it orders the comparison result, which is a logical value of 0 or 1. The resulting code is something like (pseudocode):

 ORDER BY CASE whatever = 3 THEN 1 ELSE 0 ASC, CASE whatever = 4 THEN 1 ELSE 0 ASC

What ultimately comes (if the value is in the list)

 ORDER BY 0,0,1 ORDER BY 0,1,0

and if the value is not in the list:

 ORDER BY 0,0,0

Values ​​that are not included in the list are treated with the same value and are not ordered, and the values ​​in the list are ordered depending on the position in the list.

You can replace this with ORDER BY FIELD(whatever, 3,4,6) ( documentation )

+1
source share

More articles:


All Articles