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

Question

I have some NativeQuery in my code which looks like this and where "stateValue" will be calculated and can be 1 or 0:

SELECT
        MAX(CASE 
            WHEN(? < t.someDate + interval '3 months' ) THEN 1 
            ELSE 0 
        END) AS stateValue 
    FROM
        my_table t 
    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,

I would like to sort by "stateValue" column but if I'm trying to use "stateValue" in ORDER BY I'm getting error

"ERROR: column "statevalue" does not exist"

any idea how to fix it and do properly ? Thanks!

Have a look here https://stackoverflow.com/a/25767660/4286884 — SelVazi, Jun 09 '23 at 10:14
Not sure if it's just the way you've simplified the query for this question, but I think there will only be one row returned by this query at most. No point sorting either way. — Bruno, Jun 09 '23 at 10:16

score 2 · Accepted Answer · answered Jun 09 '23 at 11:26

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

category	m	case	case
A	15	15	(null)
B	25	25	(null)

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

category	m	case	case
B	25	(null)	25
A	15	(null)	15

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.

score 0 · Answer 2 · answered Jun 09 '23 at 10:29

Regardless of what your query is returning (see @Bruno comment), You need to wrap your query within a subquery or a cte, consider the following simplification:

The following will raise an error (column "sd" does not exist):

select someDate sd 
from my_table
order by case when 1=1 then sd end desc

While the following would work:

with t as
(
  select someDate sd 
  from my_table
)
select sd
from t
order by case when 1=1 then sd end desc

demo

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

2 Answers2