Question 206051 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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.

Tags:

2

Answers


  1. 0

    You could rewrite this a limit query:

    SELECT p.id, p.title, b.price AS highest_offer
FROM products p
INNER JOIN bids b
    ON b.product = p.id
ORDER BY b.price DESC
LIMIT 1;

    If there could be two or more titles tied for the highest price, and you want to display all ties, then more generally we can use RANK here:

    WITH cte AS (
    SELECT p.id, p.title, b.price AS highest_offer,
           RANK() OVER (ORDER BY b.price DESC) rnk
    FROM products p
    INNER JOIN bids b
        ON b.product = p.id
)

SELECT id, title, highest_offer
FROM cte
WHERE rnk = 1;
    Login or Signup to reply.
  2. 0

    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';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search