Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: "Dan Winslow" <d.winslow@cox.net>
Subject: Re: Diffcult query
Date: Fri, 21 Mar 2003 15:17:02 -0800
User-Agent: KMail/1.4.3
References: <d9Iea.117330$JE5.48428@news2.central.cox.net>
	<200303211145.58768.josh@agliodbs.com>
	<00c301c2efe6$200af220$0202a8c0@dan>
In-Reply-To: <00c301c2efe6$200af220$0202a8c0@dan>
Cc: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200303211517.02945.josh@agliodbs.com>

Dan,

> No, it is not my design, unfortunately.

Then there is no simple way.=20

> > I need a ( preferably single ) query that will sum the costs for all
> unique
> > pairs of codes, without regard to column order. That is, for summing
> > purposes, rows 1 and 3 are identical, and should produce the sum of 8 f=
or
> > the unordered pair("aaa","bbb"). It should also, of course, prevent the
> case
> > where ("bbb","aaa") is considered a seperate pair. Any ideas would be m=
uch
> > appreciated.

1) Create a function which will take two text variables and concatenate the=
m=20
in alpha sort order.    The idea of this function:
fn_distinct_pair('aaa','bbb') =3D 'aaa bbb'
fn_distinct_pair('bbb','aaa') =3D 'aaa bbb'
Thus:
CREATE FUNCTION fn_distinct_pair (text, text) returns text as'
SELECT (CASE WHEN $1 < $2 THEN=20
                        $1 || '' '' || $2
                ELSE
                        $2 || '' '' || $1
                END);
' LANGUAGE SQL;
(of course, this gets much more complicated if you have more than two colum=
ns=20
or if any of the columns can be NULL)

2) Group by the results of that function.

Good luck!

-Josh Berkus