Re: filter duplicates by priority

This solved my problem. Now why didn't I think of that!? Thank you very
much everybody. This list is an incredible resource.

-Clark

> 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
>
> "Clark Slater" <pg(at)slatech(dot)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.