From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Aram Fingal <fingal(at)multifactorial(dot)com> |
Cc: | Postgres-General General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Group by with insensitive order |
Date: | 2011-01-19 21:23:32 |
Message-ID: | 27265.1295472212@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Edmundo Robles L. | 2011-01-19 21:31:53 | undefined symbol PQconnectionNeedsPassword referenced by file pg_ctl.o |
Previous Message | frank joerdens | 2011-01-19 21:18:40 | Re: debug_print_plan logs table alias used in join, not table name itself |