Menu
I have a question I came across an interesting piece of code. In MySQL statement there is something like:
{...} ORDER BY whatever=3 ASC, whatever=4 ASC, whatever=6 ASC {...}
Does that mean that it will order in 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 to be sorting only the whatever which equal to 3,4 and 6 rest is unordered...
Is this a proper/supported syntax?
778
The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.
If the values are the same in the column used in the order by, the results will be random.
Using Group By and Order By Together When combining the Group By and Order By clauses, it is important to bear in mind that, in terms of placement within a SELECT statement: The GROUP BY clause is placed after the WHERE clause. The GROUP BY clause is placed before the ORDER BY clause.
Using with multiple columnsDefine your multiple column names in ORDER BY clause separated by a comma (,). You can also specify your sorting order ASC or DESC . In the above query, I am ordering the emp_salary table by age in Ascending order and salary by descending order.
whatever=3 evaluates as a boolean with 0 as false and 1 as true.
Because it is ordered ascending the ones not meeting the condition will be ordered first. Then the second ordering column whatever=4 comes into play so ones where whatever=4 will be last in that group and so on.
For ones where whatever is not in (3,4,6) no order is specified to act as a tiebreaker and ordering is arbitrary.
It is supported syntax in MySQL but not standard or portable.
111
Actually it orders by the result of comparison, which is boolean value of 0 or 1. The resulting code is something like (pseudo-code):
ORDER BY
CASE whatever = 3 THEN 1 ELSE 0
ASC,
CASE whatever = 4 THEN 1 ELSE 0
ASC
Which in the end comes to (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
So values that are not in the list are considered with the same value and not ordered, and values in the list are ordered based on the position in the list.
You can replace this with ORDER BY FIELD(whatever, 3,4,6) (documentation)
29
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
