Get max and min value's unique id while using group by

Question

I have a table with columns: unique_id, product, root_location, price with more than 50 million records

I want the result to be product, min_price, min_price_unique_id, max_price, max_price_unique_id

My query:

select product
    , min(price) as min_price
    , max(price) as max_price
from mytable
group by product

How to get the unique id's of min and max price?

Answer 1

You could try using RANK and STRING_AGG with conditional aggregation as the following:

SELECT  product,
        MIN(price) AS min_price,
        STRING_AGG(CASE WHEN rn1 = 1 THEN unique_id END, ',') min_price_unique_id,
        MAX(price) AS max_price,
        STRING_AGG(CASE WHEN rn2 = 1 THEN unique_id END, ',') max_price_unique_id
FROM
(
  SELECT *,
    RANK() OVER (PARTITION BY product ORDER BY price) rn1,
    RANK() OVER (PARTITION BY product ORDER BY price DESC) rn2
  FROM tbl_name
) T
WHERE rn1 =1 OR rn2 =1
GROUP BY product

demo

Update: To get the unique donator_ids values in case duplicates have existed, you could use another subquery/ CTE, and use the row_number function partitioned by product, unique_id then get only rows where row_number =1.

WITH CTE1 AS
(
  SELECT *,
    RANK() OVER (PARTITION BY product ORDER BY price) rn1,
    RANK() OVER (PARTITION BY product ORDER BY price DESC) rn2
  FROM tbl_name
),
CTE2 AS
(
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY product, unique_id ORDER BY unique_id) row_num
  FROM CTE1
)
SELECT  product,
        MIN(price) AS min_price,
        STRING_AGG(CASE WHEN rn1 = 1 THEN unique_id END, ',') min_price_unique_id,
        MAX(price) AS max_price,
        STRING_AGG(CASE WHEN rn2 = 1 THEN unique_id END, ',') max_price_unique_id
FROM CTE2
WHERE (rn1 =1 OR rn2 =1) AND  row_num =1
GROUP BY product

demo