SELECT DISTINCT ON removes results

From: Guyren Howe <guyren(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: SELECT DISTINCT ON removes results
Date: 2016-10-28 20:39:19
Message-ID: AF05A411-E3A8-4317-8D6B-D4D658991C8B@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Using 9.5, this query:

SELECT o.id,
a.number AS awb
FROM pt.orders o
LEFT JOIN (
SELECT DISTINCT ON ((string_agg(air_way_bills.number::text, ','::text)))
string_agg(air_way_bills.number::text, ','::text) AS number,
air_way_bills.order_id
FROM pt.air_way_bills
GROUP BY air_way_bills.order_id) a ON a.order_id = o.id
gives me null for awb. Removing the DISTINCT ON clause:

SELECT o.id,
a.number AS awb
FROM pt.orders o
LEFT JOIN (
SELECT string_agg(air_way_bills.number::text, ','::text) AS number,
air_way_bills.order_id
FROM pt.air_way_bills
GROUP BY air_way_bills.order_id) a ON a.order_id = o.id
where o.id = 2792;
gives me an awb. I'm confused about how this can be.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2016-10-28 20:50:52 Re: SELECT DISTINCT ON removes results
Previous Message David G. Johnston 2016-10-28 20:07:22 Re: How to hint 2 coulms IS NOT DISTINCT FROM each other