Re: EXCEPT Queries

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