Andrus Andrus - 20 days ago 4x
SQL Question

How to get distinct prices by product with single row per product

Order contains same product with different prices.

How to get list of distinct prices per product in order, with one row per product?

I tried

SELECT product, string_AGG(DISTINCT price::text, ',' ORDER BY price)
FROM (VALUES ('A', 100), ('A', 200) , ('B', 200))
orderdetail (product, price)
GROUP BY product

but got error

ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
LINE 1: ...ct, string_AGG(DISTINCT price::text, ',' ORDER BY price DESC...

How to fix this ?

Postgres 9.4 is used.

This is probably required to create answer for
How to find changed prices in last two purchase invoices


Given your error message, and from what I read here on Stack Overflow from gurus like @GordonLinoff, you can't use DISTINCT inside STRING_AGG. A quick workaround would be to just subquery your table first and use DISTINCT there to remove duplicates.

SELECT t.product, STRING_AGG(t.price::text, ',' ORDER BY price)
    SELECT DISTINCT product, price
    FROM (VALUES ('A', 100), ('A', 100), ('A', 200), ('B', 200), ('B', 200))
    orderdetail (product, price)
) t
GROUP BY t.product

I tested this query on Postgres, and it returns this:

product | string_agg
text    | text
A       | 100,200
B       | 200