Re: SELECT DISTINCT ON removes results

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Fri, Oct 28, 2016 at 1:39 PM, Guyren Howe <guyren@xxxxxxxxx> wrote:

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.

​On its face the statement "DISTINCT ON removes results" is not at all surprising given its definition.

Given a self-contained query exhibiting the desired behavior I might be willing to figure out and explain exactly why its happening in that particular circumstance.

Oh, and on its face your DISTINCT ON query doesn't make any sense to me.  Using DISTINCT ON on one column but then joining on the discard-able ID column is...unusual.

And it also lacks an ORDER BY for deterministic discarding of duplicate rows.

David J.