Re: Group by with insensitive order

Aram Fingal <fingal(at)multifactorial(dot)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 the query. 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