Postgresql how to sort column which was caluclated by using "CASE… WHEN"

The query in your question on has one column, which is a aggregated expression, so you’ll only get 1 row.

Assuming you have more columns to group by, for example:

SELECT category, 
       MAX(CASE 
           WHEN(? < t.someDate + interval '3 months' ) THEN 1 
           ELSE 0 
       END) AS stateValue
    FROM
        my_table t 
    GROUP BY category

If you don’t want to repeat the full MAX(CASE ...) expression in the ORDER BY clause itself (which is indeed a good idea), you could use a CTE as @ahmed suggested:

WITH cte AS (
    SELECT category, 
       MAX(CASE 
           WHEN(? < t.someDate + interval '3 months' ) THEN 1 
           ELSE 0 
       END) AS stateValue
    FROM
        my_table t 
    GROUP BY category
)
SELECT category, stateValue
    FROM cte
    ORDER BY
        CASE WHEN LOWER(:sortColumn) = 'status' AND LOWER(:sortDirection) = 'asc' THEN stateValue END ASC,
        CASE WHEN LOWER(:sortColumn) = 'status' AND LOWER(:sortDirection) = 'desc' THEN stateValue END DESC

That might work, but I’d consider it very odd.
What’s odd is that you’re using a workaround to use parameter values to do the job of identifiers (the sort column name) and keywords (ASC and DESC).

(Detailed example below…)

Sure, it works, but it can make your SQL unnecessarily complex. I’d suspect it may also confuse the query planner in some cases.

I’m not sure what language you’re using to build this query, but you’re visibly using something outside SQL itself (maybe Java/Hibernate).

While you should indeed always use query parameters and not concatenate strings when you want to pass values into a SQL query (to avoid SQL injections), here you can actually test the sort column and sort order outside the SQL query, when you build it.

sortColumn and sortOrder don’t need to be used as values in your SQL query (and you’re literally comparing them to literals within the query you’ve constructed at the moment).

query = """
WITH cte AS (
  SELECT category, MAX(val) AS stateValue
     FROM table1
     GROUP BY category
)
SELECT *
   FROM cte
""";

if ("status".equalsIgnoreCase(sortColumn)) {
    query += " ORDER BY stateValue";
    if ("desc".equalsIgnoreCase(sortOrder)) {
        query += " DESC";
    }
}

The main distinction here is that sortDirection and sortOrder will never be or refer to any values in your tables or expressions. Rather, they’re used to determine which identifiers and keywords (ASC/DESC) need to be used in the query.

Of course, make sure you don’t concatenate these variables directly into the query string (again, to avoid SQL injection), but there’s nothing wrong with using external code to use literals to build identifiers in your query string.

Detailed example:

Essentially, you’re relying on the fact that,

• when LOWER(:sortColumn) = 'status' AND LOWER(:sortDirection) = 'asc' is false,
• then CASE WHEN LOWER(:sortColumn) = 'status' AND LOWER(:sortDirection) = 'asc' THEN stateValue END is NULL,
• so the first sort expression is null and it will rely on the second sort expression to do the sorting CASE WHEN LOWER(:sortColumn) = 'status' AND LOWER(:sortDirection) = 'desc' THEN stateValue END DESC

Assuming your example looks like this:

CREATE TABLE table1 (
    id SERIAL PRIMARY KEY,
    category TEXT NOT NULL,
    val INTEGER
);

INSERT INTO table1(category,val)
VALUES ('A', 1), ('A', 10), ('A', 15),
       ('B', 4), ('B', 9), ('B', 25);

Expanded, when sortDirection is asc, it looks like this:

WITH cte AS (
  SELECT category, MAX(val) AS m
     FROM table1
     GROUP BY category
)
SELECT *,
       CASE WHEN LOWER('status')='status' AND LOWER('asc')='asc' THEN m END,
       CASE WHEN LOWER('status')='status' AND LOWER('asc')='desc' THEN m END
   FROM cte
ORDER BY
   CASE WHEN LOWER('status')='status' AND LOWER('asc')='asc' THEN m END ASC,
   CASE WHEN LOWER('status')='status' AND LOWER('asc')='desc' THEN m END DESC

Here, the first order expression is used and all the values in the second one are null, so it’s irrelevant.

When sortDirection is desc

WITH cte AS (
  SELECT category, MAX(val) AS m
     FROM table1
     GROUP BY category
)
SELECT *,
       CASE WHEN LOWER('status')='status' AND LOWER('desc')='asc' THEN m END,
       CASE WHEN LOWER('status')='status' AND LOWER('desc')='desc' THEN m END
   FROM cte
ORDER BY
   CASE WHEN LOWER('status')='status' AND LOWER('desc')='asc' THEN m END ASC,
   CASE WHEN LOWER('status')='status' AND LOWER('desc')='desc' THEN m END DESC

Here, the values in the first order expression are all null so treated at the same leve, so the values in the second expression are then used.