ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

Question

I have a table "campaign_items" with the columns (budget, spent) and I want to calculate the remaining budget using the formula remaining budget = budget - spent

Right now I am running below query :

select distinct a.budget,a.spent 
from campaign_items a 
where campaign_item_id=12345 
order by a.budget-a.spent

But I am getting the error :

ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

Note : I cannot remove the DISTINCT keyword from the query because query is generated using JdbcTemplate

Could anyone help me to sort out this error?

Answer 1

I think the root cause of the error is that your are ordering using ORDER BY a.budget - a.spent, but this expression does not appear in the SELECT clause. In the query below, I use a subquery containing a computed column for sorting, but then only select out the budget and spent columns.

select t.budget, t.spent
from
(
    select distinct a.budget,
                    a.spent,
                    a.budget - a.spent as sort,
    from campaign_items a
    where campaign_item_id = 12345
) t
order by t.sort