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