Re: filter duplicates by priority

"Clark Slater" <pg@slatech.com> writes:
> I am trying to use DISTINCT ON to filter out *potential* duplicate values
> from a set of sub queries.  There are certain cases where there can be
> repetitive part numbers that are priced differently.  I'm trying to start
> with the full list, ordered by priority, and then remove any repeats that
> have a lesser priority.

> SELECT DISTINCT ON (part_number) * FROM (
> SELECT part_number, priority FROM ...
> UNION ALL
> SELECT part_number, priority FROM ...
> UNION ALL
> SELECT part_number, priority FROM ...
> ) AS filter_duplicates ORDER BY priority,part_number

> The above statement does not work because if I ORDER BY
> priority,part_number then I have to DISTINCT ON (priority,part_number).
> But DISTINCT ON (priority, part_number) does not remove the repeated rows
> because the same part_number with a different priority becomes a distinct
> tuple.

AFAICS, changing it to ORDER BY part_number,priority would solve the
stated problem.  If you really need the final result in priority rather
than part number order, put the whole thing in a sub-select and re-sort
outside it.

            regards, tom lane