From: Josh Berkus <josh@agliodbs.com>
To: Dan Winslow <d.winslow@cox.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Diffcult query
Date: Fri, 21 Mar 2003 15:17:02 -0800
Message-ID: <200303211517.02945.josh@agliodbs.com> (raw)
In-Reply-To: <00c301c2efe6$200af220$0202a8c0@dan>
References: <d9Iea.117330$JE5.48428@news2.central.cox.net>
<200303211145.58768.josh@agliodbs.com>
<00c301c2efe6$200af220$0202a8c0@dan>
Dan,
> No, it is not my design, unfortunately.
Then there is no simple way.
> > 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 for
> > 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 much
> > appreciated.
1) Create a function which will take two text variables and concatenate them
in alpha sort order. The idea of this function:
fn_distinct_pair('aaa','bbb') = 'aaa bbb'
fn_distinct_pair('bbb','aaa') = 'aaa bbb'
Thus:
CREATE FUNCTION fn_distinct_pair (text, text) returns text as'
SELECT (CASE WHEN $1 < $2 THEN
$1 || '' '' || $2
ELSE
$2 || '' '' || $1
END);
' LANGUAGE SQL;
(of course, this gets much more complicated if you have more than two columns
or if any of the columns can be NULL)
2) Group by the results of that function.
Good luck!
-Josh Berkus
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: josh@agliodbs.com, d.winslow@cox.net
Subject: Re: Diffcult query
In-Reply-To: <200303211517.02945.josh@agliodbs.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