Re: filter duplicates by priority

On Tue, Jul 14, 2009 at 10:04:12AM -0400, Clark Slater wrote:
> 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.

I think you just want to swap the ORDER BY columns around; i.e:

ORDER BY part_number, priority

--
Sam http://samason.me.uk/