Question 421019 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How can I have `CASE` in the second key of the `ORDER BY` in postgres sql?

In Postgres, I am trying to have a SQL which orders by multiple columns. The first column will always be priority DESC NULLS LAST. The second one needs to be dynamic based on some conditions (for ease of example, here I have WHEN 1 = 1).

SELECT id, title, priority
FROM post
ORDER BY 
    priority DESC NULLS LAST,
    CASE WHEN 1 = 1 THEN created_at ASC ELSE created_at DESC END;

This throws an error:

syntax error at or near "ASC"
LINE 5:         CASE WHEN 1 = 1 THEN created_at ASC ELSE created_at ...

How can I have CASE in the second key of the ORDER BY?

Tags:

2

Answers


  1. 0

    You can not use DESC or ASC in case when … else … end.
    This is ORDER BY part: ORDER BY <expression> DESC.

    Try use date difference with any anchor date – integer value.

    SELECT id, title, priority
FROM post
ORDER BY 
    priority DESC NULLS LAST,
    CASE WHEN 1 = 1 THEN created_at-'2025-01-01'::date   -- ASC
    ELSE '2025-01-01'::date-created_at  END; -- DESC

    Or convert date to bigint and use sign (+/-)

    SELECT id, title, priority
FROM post
ORDER BY 
    priority DESC NULLS LAST,
CASE WHEN 1 = 1 THEN EXTRACT(EPOCH FROM created_at)   -- ASC
    ELSE -EXTRACT(EPOCH FROM created_at)  END; -- DESC

    Fiddle

    Login or Signup to reply.
  2. 0

    You can’t use ASC or DESC directly in the CASE statement, same is also described in below stack overflow post

    Case statement for Order By clause with Desc/Asc sort

    Instead you can do below

    SELECT id, title, priority, created_at
FROM post
ORDER BY 
    priority DESC NULLS LAST,
    CASE 
        WHEN 1 = 1 THEN created_at
        ELSE NULL -- Or provide a default value if needed
    END ASC
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search