Re: Group by with insensitive order

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