Menu
I have a MySQL table and would like to return the rows based on a column value in this order:
E.g. 0,2,4,7,-2,-3,-5
569
You can also ORDER BY two or more columns, which creates a nested sort . The default is still ascending, and the column that is listed first in the ORDER BY clause takes precedence. The following query and Figure 3 and the corresponding query results show nested sorts.
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.
A simple solution is to use another array. We copy all elements of the original array to a new array. We then traverse the new array and copy all negative and positive elements back into the original array one by one.
If you specify multiple columns, the result set is sorted by the first column and then that sorted result set is sorted by the second column, and so on. The columns that appear in the ORDER BY clause must correspond to either column in the select list or columns defined in the table specified in the FROM clause.
Can use SIGN to sort the positive numbers to the top, then take the absolute value with ABS to get the desired ASC/DESC.
SELECT * FROM theTable
ORDER BY SIGN(col) DESC, ABS(col)
EDIT
As Nahuel pointed out, the above will sort 0's to the middle between positive and negative. To instead group them with the positives, you can use a CASE instead (or, if your column is only integers, the slightly magical SIGN(col + 1))
SELECT * FROM theTable
ORDER BY
CASE WHEN col >= 0 THEN 1 ELSE 2 END,
ABS(col)
SELECT columnName1 FROM Tbl
WHERE columnName1 >= 0
ORDER BY columnName1 ASC
UNION
SELECT columnName1 FROM Tbl
WHERE columnName1 < 0
ORDER BY columnName1 DESC
Should work
34
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
