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" UNION SELECT 2, subscriber_name, account_number FROM "B" ORDER BY 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 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
