On Thu, 20 Feb 2003 01:22:33 -0500, Mark Mitchell wrote:
> Here is an example of what I'm currently doing.
>
> TABLE "A"
> "SUBSCRIBER_NAME" | "ACCOUNT_NUMBER"
> -------------------------------------- BOB | 000001 JOE
> | 000002
>
> TABLE "B"
> "SUBSCRIBER_NAME" | "ACCOUNT_NUMBER"
> -------------------------------------- BOB | 000001
>
> To dedup table "A" using the data in table "B" I could use the
> following, except that the dedup takes place on the whole row when I
> only want it to take place on the "ACCOUNT_NUMBER" column.
>
> SELECT
> "A"."SUBSCRIBER_NAME" , "A"."ACCOUNT_NUMBER" FROM "A" EXCEPT
> SELECT
> "B"."SUBSCRIBER_NAME" , "B"."ACCOUNT_NUMBER" FROM "B"
>
>
How about a SELECT DISTINCT ON?
SELECT DISTINCT ON (account_number)
subscriber_name, account_number
FROM
(SELECT 1 AS sort_order, subscriber_name, account_number FROM "A"UNIONSELECT 2, subscriber_name, account_number FROM
"B"ORDERBY sort_order) as tmp
ORDER BY account_number;
(Untested, but it follows a pattern I've learned.)
--
Jeff Boes vox 269.226.9550 ext 24
Database Engineer fax 269.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise