1

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

Community
  • 1
  • 1
Andrus
  • 26,339
  • 60
  • 204
  • 378
  • 1
    price is second column in orderdetail table ::text is postgres cast which changes its type to text to get string price list. It can probably written as `cast(price as text)` in standard sql – Andrus Oct 04 '16 at 06:07
  • 1
    When you say 'How to get list of distinct prices per product in order?', do you mean with a single row per product? – Robin Mackenzie Oct 04 '16 at 06:08
  • Yes, single row per product containing list of different prices in ascending order. Second column can probably be also array instead of string – Andrus Oct 04 '16 at 06:09

1 Answers1

2

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)
FROM
(
    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
Community
  • 1
  • 1
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360