X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id CE2C5474E5C
	for <pgsql-sql@postgresql.org>; Thu, 20 Feb 2003 20:36:25 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 77968-07 for <pgsql-sql@postgresql.org>;
	Thu, 20 Feb 2003 20:36:15 -0500 (EST)
Received: from news.hub.org (news.hub.org [64.49.215.80])
	by postgresql.org (Postfix) with ESMTP id 152BD474E4F
	for <pgsql-sql@postgresql.org>; Thu, 20 Feb 2003 20:36:15 -0500 (EST)
Received: by news.hub.org (Postfix, from userid 8)
	id E244943A679; Thu, 20 Feb 2003 20:36:14 -0500 (EST)
From: Jeff Boes <jboes@nexcerpt.com>
Subject: Re: EXCEPT Queries
Date: Thu, 20 Feb 2003 15:59:19 -0500
X-Newsgroups: comp.databases.postgresql.sql
References: <3E53D806.5030501@lapcrew.com>
User-Agent: Pan/0.11.4 (Unix)
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
X-Comment-To: "Mark Mitchell" <mark@lapcrew.com>
Message-ID: <3e553c80$2_5@news.teranews.com>
Lines: 42
X-Abuse-Report: Send abuse reports to abuse@teranews.com
Organization: http://www.TeraNews.com - FREE NNTP Access
To: pgsql-sql@postgresql.org
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200302/468
X-Sequence-Number: 11866

On Thu, 20 Feb 2003 01:22:33 -0500, Mark Mitchell wrote:

> Here is an example of what I'm currently doing.
>=20
> TABLE "A"
> "SUBSCRIBER_NAME" | "ACCOUNT_NUMBER"
> -------------------------------------- BOB               |  000001 JOE=20=
=20
>             |  000002
>=20
> TABLE "B"
> "SUBSCRIBER_NAME" |  "ACCOUNT_NUMBER"
> -------------------------------------- BOB               |   000001
>=20
> 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.
>=20
> SELECT
> "A"."SUBSCRIBER_NAME" , "A"."ACCOUNT_NUMBER" FROM "A" EXCEPT
> SELECT
> "B"."SUBSCRIBER_NAME" , "B"."ACCOUNT_NUMBER" FROM "B"
>=20
>=20

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.)

--=20
Jeff Boes                                      vox 269.226.9550 ext 24
Database Engineer                                     fax 269.349.9076
Nexcerpt, Inc.                                 http://www.nexcerpt.com
           ...Nexcerpt... Extend your Expertise