Re: Group by with insensitive order

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