From: Jeff Boes <jboes@nexcerpt.com>
To: pgsql-sql@postgresql.org
Subject: Re: EXCEPT Queries
Date: Thu, 20 Feb 2003 15:59:19 -0500
Message-ID: <3e553c80$2_5@news.teranews.com> (raw)
References: <3E53D806.5030501@lapcrew.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jboes@nexcerpt.com
Subject: Re: EXCEPT Queries
In-Reply-To: <3e553c80$2_5@news.teranews.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox