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@xxxxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
