Search code examples
node.jspostgresqlbackendnest

How to use postgres subquery result to filter overall results

I have this simple query

 SELECT c1.id AS id,
       c1.title AS title,
         (
    SELECT  b1.price
    FROM bids b1
    WHERE b1.product = c1.id
    ORDER BY b1.price DESC
    LIMIT 1
  ) AS highest_offer
  
  FROM products c1
ORDER BY highest_offer

and getting results like this

however If I want to add this query

WHERE highest_offer = '538.16'

I gets error :

 error: column "highest_offer" does not exist

Please help me

I tried different things but nothing worked.

Solution

  • The simplest way would be to use your query as a tabular subquery in a from clause

    select * from 
(
  /* your query here */ 
) as t
WHERE t.highest_offer = '538.16';

    or in a CTE

    with t as 
(
  /* your query here */ 
)
select * from t  
WHERE highest_offer = '538.16';