'MySql: Order randomly when order is 0, but use numbers before

I have this table enter image description here

And I wanna retrieve all data but in a certain order. I want it to be orderd by the field order, but with 0 meaning at random and AFTER the actual numbers in that field have been ordered. So if I did this:

var sqlString = "SELECT * \
FROM 10561_13581_tblOffers \
WHERE isVisible = 1 \
LIMIT " + start + "," + count

I want the result to be either:

"bücher oder so" - order 1 "frau mit haar" - order 3 "stein" - order 0 "ordermy" - order 0

OR the last two reversed, as they are supposed to be randomly ordered

"bücher oder so" - order 1 "frau mit haar" - order 3 "ordermy" - order 0 "stein" - order 0

But in both cases, order 1 and 3 are in order.

How would I make my query to have the desired result? (ofc, this can be done with a lot more order numbers and a lot more rows with a 0 for order)

mysql


Solution 1:[1]

The boolean expression order = 0 is evaluated as 1 for true or 0 for false and can be used in the ORDER BY clause:

SELECT * 
FROM 10561_13581_tblOffers 
WHERE isVisible = 1
ORDER BY `order` = 0, --this will send all 0s at the end of the resultset
         `order`

If you actually want all 0s to be sorted randomly you can add RAND() function:

ORDER BY `order` = 0, 
         `order`,
         RAND()

Solution 2:[2]

You can use

ORDER BY CASE 
  WHEN order = 0 THEN 99999999
  ELSE order END

like that the 0's will come at the end in no particular order.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 forpas
Solution 2

Related Questions