How to sort by a calculated column in PostgreSQL?

Question

We have a number of fields in our offers table that determine an offer's price, however one crucial component for the price is an exchange rate fetched from an external API. We still need to sort offers by actual current price.

For example, let's say we have two columns in the offers table: exchange and premium_percentage. The exchange is the name of the source for the exchange rate to which an external request will be made. premium_percentage is set by the user. In this situation, it is impossible to sort offers by current price without knowing the exchange rate, and that maybe different depending on what's in the exchange column.

How would one go about this? Is there a way to make Postgres calculate current price and then sort offers by it?

Edit your question and provide sample data and desired results. — Gordon Linoff
– Gordon Linoff, Commented Oct 21, 2017 at 3:34

Craig Ringer · Accepted Answer · 2017-10-21 03:35:38Z

13

SELECT
    product_id,
    get_current_price(exchange) * (premium_percentage::float/100 + 1) AS price
FROM offers
ORDER BY 2;

Note the ORDER BY 2 to sort by the second ordinal column.

You can instead repeat the expression you want to sort by in the ORDER BY clause. But that can result in multiple evaluation.

Or you can wrap it all in a subquery so you can name the output columns and refer to them in other clauses.

SELECT product_id, price
FROM
(
    SELECT 
        product_id,
        get_current_price(exchange) * (premium_percentage::float/100 + 1)
    FROM offers
) product_prices(product_id, price)
ORDER BY price;

answered Oct 21, 2017 at 3:35

Craig Ringer

329k84 gold badges743 silver badges821 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

klin Over a year ago

In the ORDER BY clause you can use output column names, so it can also be ORDER BY price in the first query.

Craig Ringer Over a year ago

@klin Did you try it? You actually can't, because price isn't a column of any table, it's an alias for a calculated column in the select result-list. Some databases do allow you to do it anyway, but it's not SQL-standard, and PostgreSQL isn't one of the ones that permits it.

klin Over a year ago

From the documentation: An output column's name can be used to refer to the column's value in ORDER BY and GROUP BY clauses... SqlFiddle.

viktor.svirskyy Over a year ago

What about performance in this case, especially talking about big data set?

Craig Ringer Over a year ago

@viktor.svirskyy It'll generally be awful, because PostgreSQL will have no way to avoid executing the sort expression on every row in order to determine which rows are of interest for subsequent limits, filters, etc. You should consider a functional index on the expression.

Collectives™ on Stack Overflow

How to sort by a calculated column in PostgreSQL?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related