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

I have this table

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)

CodePudding user response：

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.

CodePudding user response：

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()