How to order by if else condition in room android?

Question

I have a list of items each item has ranking. Some rankings are zero. I want to query items by ranking ascending order if it is not zero and then remaining items which has ranking equal to zero. I hope it is understandable. :))

data class Item(
    val id:Int,
    val ranking:Int
)

Answer 1

You can use the boolean expression ranking > 0 which evaluates to 1 for true and 0 for false in the ORDER BY clause:

SELECT *
FROM tablename 
ORDER BY ranking > 0 DESC, ranking;

See a simplified demo.