Обсуждение: Group by with insensitive order

Suppose I'm doing a group by query like the following:

SELECT drug1, drug2, AVG(response)
FROM data
GROUP BY drug1, drug2

The problem is that the same drug may appear sometimes as drug1 and sometimes as drug2.  So, for example, the
combination"aspirin, acetaminophen" may also appear as "acetaminophen, aspirin" and I want these grouped together in
thequery.  Is there a simple way to do this? 

--Aram Fingal

On Wed, Jan 19, 2011 at 03:37:58PM -0500, Aram Fingal wrote:
> Suppose I'm doing a group by query like the following:
>
> SELECT drug1, drug2, AVG(response)
> FROM data
> GROUP BY drug1, drug2
>
> The problem is that the same drug may appear sometimes as drug1 and
> sometimes as drug2.  So, for example, the combination "aspirin,
> acetaminophen" may also appear as "acetaminophen, aspirin" and I want
> these grouped together in the query.  Is there a simple way to do
> this?

instead of drug1 write: least(drug1, drug2) and instead of drug2 write:
greatest(drug1, drug2) - both in select and group by.

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

Aram Fingal <fingal@multifactorial.com> writes:
> Suppose I'm doing a group by query like the following:
> SELECT drug1, drug2, AVG(response)
> FROM data
> GROUP BY drug1, drug2

> The problem is that the same drug may appear sometimes as drug1 and sometimes as drug2.  So, for example, the
combination"aspirin, acetaminophen" may also appear as "acetaminophen, aspirin" and I want these grouped together in
thequery.  Is there a simple way to do this? 

Easy way is something like

SELECT LEAST(drug1, drug2), GREATEST(drug1, drug2), AVG(response)
FROM data
GROUP BY 1, 2

though it'd be a PITA to scale that to more than 2 drugs.

            regards, tom lane

> Easy way is something like
>
> SELECT LEAST(drug1, drug2), GREATEST(drug1, drug2), AVG(response)
> FROM data
> GROUP BY 1, 2
>
> though it'd be a PITA to scale that to more than 2 drugs.
>
>             regards, tom lane

Thanks, Tom and Hubert, who said the same thing.  For the foreseeable future, two drug combos are all we're going to be
doing. That's bad enough with over 8000 combos of two.  

-Aram