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?

Answer 1

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;