SQLITE Conditional Order By with CASE

Question

I want to order an SQLITE Query in different orders depending on the boolean paid.

The query works without the case statement.

The due column is of type long representing a date in unix time.

Withe the following query, I'm getting the following error:

extraneous input 'ELSE' expecting {, ';', K_ALTER, K_ANALYZE, K_ATTACH, K_BEGIN, K_COMMIT, K_CREATE, K_DELETE, K_DETACH, K_DROP, K_END, K_EXPLAIN, K_INSERT, K_PRAGMA, K_REINDEX, K_RELEASE, K_REPLACE, K_ROLLBACK, K_SAVEPOINT, K_SELECT, K_UPDATE, K_VACUUM, K_VALUES, K_WITH, UNEXPECTED_CHAR}

SELECT transactions.* FROM transactions
    ORDER BY
    paid ASC,
    CASE paid WHEN 0
        THEN due DESC
        ELSE due ASC
    END,
    date DESC, title ASC

Thanks!

Answer 1

You could use:

SELECT *
FROM transactions
ORDER BY
    paid,
    CASE paid WHEN true THEN -1.0*due ELSE due END,
    date DESC,
    title;

This answer assumes that due is some sort of long timestamp, and then the second level of sorting in your expected ORDER BY clause should place more recent records first, when paid is true, and the reverse when paid is not true.