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!

Please show sample data and desired results. Also elaborate on what the two tables are. — Gordon Linoff
– Gordon Linoff, Commented Oct 12, 2020 at 12:15

GMB · Accepted Answer · 2020-10-12 12:23:53Z

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)

edited Oct 12, 2020 at 12:23

answered Oct 12, 2020 at 12:15

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shanmukh Meesala Over a year ago

My bad... I edited the description. Is there a way I can get all in a single row instead of two rows? Thanks for your quick response.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related