PostgreSQL - How to get min and max values of a column and the rows associated with them in a single query?

Question

I have a table called 'product' with columns - product_name, sale_price, sale_date

I want to get min(sale_price) and max(sale_price) and the dates in which sales of min and max prices happened.

I have my query like this:

SELECT sale_price, sale_date FROM product WHERE (sale_price) IN (
            SELECT 
               min(sale_price)
            FROM product
            WHERE product_name = 'PHONE'
            ) AND product_name = 'PHONE'
    UNION
SELECT sale_price, sale_date FROM product WHERE (sale_price) IN (
            SELECT 
               max(sale_price)
            FROM product
            WHERE product_name = 'PHONE'
            ) AND product_name = 'PHONE'

I am sure that there's an elegant way to query this. Any help is much appreciated. Thanks in advance!

Answer 1

You can use window functions:

select sale_price, sale_date 
from (
    select p.*, 
        rank() over(order by sale_price) rn_asc,
        rank() over(order by sale_price desc) rn_desc
    from product p
    where product_name = 'PHONE'
) p
where 1 in (rn_asc, rn_desc)

This answer is based on your description of the problem. You did not explain what table daily_price represents in the query, so the query does not uses it.

If you want the results on a single row, then you can do conditional aggregation:

select 
    min(sale_price) min_sale_price, min(sale_date) filter(where rn_asc  = 1) min_sale_date,
    max(sale_price) min_sale_price, max(sale_date) filter(where rn_desc = 1) max_sale_date
from (
    select p.*, 
        rank() over(order by sale_price) rn_asc,
        rank() over(order by sale_price desc) rn_desc
    from product p
    where product_name = 'PHONE'
) p
where 1 in (rn_asc, rn_desc)