CODEWITHSUNDEEP
postgresql
Jared
Jared

Reputation: 6080

PostgreSQL use function result in ORDER BY

Is there a way to use the results of a function call in the order by clause?

My current attempt (I've also tried some slight variations).

SELECT it.item_type_id, it.asset_tag, split_part(it.asset_tag, 'ASSET', 2)::INT as tag_num
FROM serials.item_types it
WHERE it.asset_tag LIKE 'ASSET%'
ORDER BY split_part(it.asset_tag, 'ASSET', 2)::INT;

While my general assumption is that this can't be done, I wanted to know if there was a way to accomplish this that I wasn't thinking of.

EDIT: The query above gives the following error [22P02] ERROR: invalid input syntax for integer: "******"

Upvotes: 0

Views: 94

Answers (1)

yieldsfalsehood
yieldsfalsehood

Reputation: 3085

Your query is generally OK, the problem is that for some row the result of split_part(it.asset_tag, 'ASSET', 2) is the string ******. And that string cannot be cast to an integer.

You may want to remove the order by and the cast in the select list and add a where split_part(it.asset_tag, 'ASSET', 2) = '******', for instance, to narrow down that data issue.

Once that is resolved, having such a function in the order by list is perfectly fine. The quoted section of the documentation in the comments on the question is referring to applying an order by clause to the results of UNION, INTERSECTION, etc. queries. In other words, the order by found in this query:

(select column1 as result_column1 from table1
 union
 select column2 from table 2)
order by result_column1

can only refer to the accumulated result columns, not to expressions on individual rows.

Upvotes: 2

Related Questions