From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Clark Slater" <pg(at)slatech(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: filter duplicates by priority |
Date: | 2009-07-14 15:48:56 |
Message-ID: | 23993.1247586536@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"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.
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
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2009-07-14 15:56:30 | Re: filter duplicates by priority |
Previous Message | Hartman, Matthew | 2009-07-14 15:30:38 | Re: filter duplicates by priority |