Conditional sorting using order

Question

When sorting based on a condition in a procedure:

    ...
    SELECT 
    ...
    order by formattedbookingdate 
    CASE WHEN isasc IS TRUE THEN
    asc
    ELSE
    desc
    END;
    ...

Postgres generates an error: ERROR: syntax error in or near "CASE" LINE 266: CASE WHEN isasc IS TRUE THEN

Answer 1

I would separate the conditions, so after completion add ascor desc, for example:

td=# select * from (select generate_series(1,3) a) a order by case when true then a end asc, case when true then a end desc;
 a
---
 1
 2
 3
(3 rows)

td=# select * from (select generate_series(1,3) a) a order by case when false then a end asc, case when true then a end desc;
 a
---
 3
 2
 1
(3 rows)

And yes, I would think again about why I would do this :)

Answer 2

This will probably be slow, but it works:

SELECT 
    ...
    order by
    CASE WHEN isasc IS TRUE THEN
    formattedbookingdate
    ELSE
    TIMESTAMP 'epoch' - formattedbookingdate
    END;

Fiddle