Room Database - Secondary ORDER BY not working Java

Question

I have a RecyclerView list of CardViews for tasks, etc. Some of the CardViews have a due date attached to them, others have no due date. I want to show the CardViews with due dates on top of the list, sorted by nearest to farthest due date. This is working as expected.

I want the CardViews without due dates to show at the bottom of the list, and be sorted by their Card IDs from highest to lowest, but this is not working as expected. The bottom of the list is showing the lowest CardView ID first and then higher number Cards below it.

Here is the Room code:

@Query("SELECT * FROM cards_table WHERE cardType = :cardType0 ORDER BY " +
    "CASE WHEN cardDuedatentime = -1 THEN 1 END, cardId DESC, " +
    "CASE WHEN cardDuedatentime != -1 THEN cardDuedatentime END ASC "
)       
List<Card> getFilteredCards0(String cardType0);

The graphic shows the card numbers with Card #1 above #2. I would like that to be reversed with Card #2 above Card #1, etc.

What am I missing here?

user2722968 · Accepted Answer · 2025-09-15 18:47:28Z

2

Two things:

First, it seems the RecyclerView is in reversed order of the how the query actually returns its data.

Second, the order in the query does not do what you think it does. As sqlite sorts NULL as smaller than any other value, the expression CASE WHEN cardDuedatentime = -1 THEN 1 END sorts all rows without a due-date first (because NULL is smaller than 1), and with a due-date second. Within those groups, by cardId in reversed order (cardId DESC), which makes the order unique (because cardId is unique (?!)); the third ordering expression is useless, because no rows can share the same cardId but differ in cardDuedatentime. This results in the order 2 - 1 - 4 - 3, as 2 and 1 do not have a due-date and are ordered by their id in reversed order, and 4 and 3 have a due-date and are also ordered by their id in reversed order. The fact that the RecyclerView seems to be sorted by due-date is a coincidence.

The ORDER BY clause you are looking for is probably CASE WHEN cardDuedatentime != -1 THEN cardDuedatentime END ASC NULLS LAST, cardId DESC. You'll have to solve the order being "flipped" in RecyclerView by other means; I don't know anything about that.

answered Sep 15 at 18:47

user2722968

17.4k5 gold badges58 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

AJW Sep 15 at 19:52

Ok I will give your CASE a try. Is there any restriction on using "NULS LAST" in Android versions or the DAO/Room abstraction layer?

user2722968 Sep 15 at 20:06

I don't know, but its a standard part of sqlite's sql-dialect.

AJW Sep 15 at 22:55

Ok I was able to utilize insights from your answer to set up two new CASE statements and all working as expected now. Answer accepted and upvoted, cheers!

MikeT · Accepted Answer · 2025-09-16 00:21:05Z

Perhaps consider an alternative approach that does not need the CASE THEN ELSE assuming that the cardduedatetime adheres to the specified datetime formats as per SQLite date time formats (according to you output the dates do appear to at least be sortable and therefore are close to such dates)

e.g. :-

SELECT *
FROM cards_table 
ORDER BY
    /* coalesce so null date (invalid datetime returns null)*/
    /* if null then use the maxdatetime less the cardid */
    coalesce(strftime('%s',cardduedatetime),strftime('%s','3000-12-31 23:59') /*number larger tha*/)-cardid ASC;

comments added to explain
the coalesce function is basically an if-then construct used to provide a sortable value in either case.

Using the following to demonstrate:-

/* just in case cleanup*/
DROP TABLE IF EXISTS cards_table;
/* create the table*/
CREATE TABLE IF NOT EXISTS cards_table (cardid INTEGER PRIMARY KEY, cardtype TEXT, cardduedatetime TEXT, cardtitle TEXT);
/* populate the table as per the question */
INSERT INTO cards_table VALUES 
    (1,'Buy', NULL,'TEST 1')
    ,(2,'Buy',NULL,'TEST 2')
    ,(3,'Buy','2025-09-24 23:59','TEST 3')
    ,(4,'Buy','2025-09-26 23:59','TEST 4')
;
SELECT *, 
    /* for demonstration show the derived sortable value */
    coalesce(strftime('%s',cardduedatetime),strftime('%s','3000-12-31 23:59') /*number larger tha*/)-cardid AS showval
FROM cards_table 
ORDER BY
    /* coalesce so null date (invalid datetime returns null)*/
    /* if null then use the maxdatetime less the cardid (which will be greater than an actual date)*/
    coalesce(strftime('%s',cardduedatetime),strftime('%s','3000-12-31 23:59') /*number larger tha*/)-cardid ASC;
/* cleanup after enviroment after the test  */
DROP TABLE IF EXISTS cards_table;

Then the output is:-

The showval column included to demonstrate the value that is sorted i.e. IF valid datetime THEN that datetime ELSE the date 12/31/3000 (US format) in either case less the card id thus for invalid dates the latest cardid will appear higher in the list.

likewise valid datetimes that are the same
instead of strftime('%s','3000-12-31 23:59') a hard coded high value could be used such as 9999999999. However, the former is perhaps clearer to understand.
note the showval column for the last two and how the cardid is subtracted to show the latest (higher cardid) higher in the output.

Note shown/indicated as an issue if a row with the same datetime appears such as one inserted using ,(5,'Buy','2025-09-26 23:59','TEST 5') /*demonstrate same duedatetime*/ then that would appear before TEST 4 e.g. :-

I appreciate the tables to show the output...will test with my data. I've stored my dates as longs, with milliseconds since the epoch.
Storing as long with milliseconds would need to be handled a little differently for it to be valid for use by strftime. Divide by 1000 to drop the milliseconds and then use the unixepoch modifier (see sqlite.org/lang_datefunc.html#modifiers), otherwise it will be considered invalid and therefore null.

Collectives™ on Stack Overflow

Room Database - Secondary ORDER BY not working Java

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related