How to order by if else condition in room android?-CodePudding

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
)

CodePudding user response：

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.

CodePudding user response：

One option would be, to query twice and then merge the result sets.

@Query("SELECT ... WHERE ranking > 0 ORDER BY ranking DESC")
List<Item> getRankings();

@Query("SELECT ... WHERE ranking = 0")
List<Item> getUnranked();

One could also query once, but this needs pointless processing, because one needs to return MAX(ranking) 1 instead of 0, so that the desired sorting would be possible. Without having benchmarked that, not utilizing the domain aggregate on each single row should be quicker. I'd rather add an @Index on field ranking, in order to speed up the query execution.