CODEWITHSUNDEEP
sqlpostgresqldynamic-sqlpostgresql-16
sudoExclamationExclamation
sudoExclamationExclamation

Reputation: 8690

Switch ASC / DESC in ORDER BY with a CASE construct?

In PostgreSQL, I am trying to have 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?

Upvotes: 1

Views: 97

Answers (4)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656153

ASC / DESC are key words of the SQL language. You cannot switch those per value of a column or parameter input in a prepared statement. See:

You can work around this restriction with smart tricks like demonstrated by jarlh, or ValNik for data types with a well-defined negator (like - for numeric types).

Typically, this invalidates the use of plain indexes, though.

You need dynamic SQL to do this properly. Conditionally formulate the query in your client or use more versatile server-side dynmaic SQL. Like:

CREATE OR REPLACE FUNCTION foo(_created_at_asc_desc bool = true)
  RETURNS TABLE (
    id       int   -- ADAPT types to actual output!
  , title    text
  , priority int
  )
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE format(
   'SELECT id, title, priority
    FROM   public.post  -- schema-qualify to be safe
    ORDER  BY priority DESC NULLS LAST, created_at %s'
 , CASE WHEN $1 THEN 'ASC' ELSE 'DESC' END  -- enforce valid syntax
   );
END
$func$;

Call:

SELECT * FROM foo();  -- defaults to ASC
SELECT * FROM foo(false);

Related:

Upvotes: 0

jarlh
jarlh

Reputation: 44696

If the 1 = 1 condition is constant for the whole query, you can ORDER BY two separate columns; do ASC for true, and do DESC for false:

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

Upvotes: 1

Syed
Syed

Reputation: 79

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

Case expression 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

The PostgreSQL CASE statement is used to return a value based on conditions. It is not designed to handle modifiers like ASC or DESC.

Reference:

https://www.postgresql.org/docs/current/functions-conditional.html

CASE clauses can be used wherever an expression is valid. Each condition is an expression that returns a boolean result. If the condition's result is true, the value of the CASE expression is the result that follows the condition, and the remainder of the CASE expression is not processed.

Upvotes: -1

ValNik
ValNik

Reputation: 5493

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

Upvotes: 3

Related Questions