aggregates, distinct, order by, and case - why won't this work

From: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: aggregates, distinct, order by, and case - why won't this work
Date: 2015-10-02 20:52:50
Message-ID: CAKFQuwb0VYBFk_Fnjbi4tbYRVsAXWD1xNOnzF=N=h=CR0z9DGg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

This...on 9.3

SELECT array_agg(
distinct case when v % 2 = 0 then 'odd' else 'even' end
order by case when v % 2 = 0 then 1 else 2 end
)
FROM (VALUES (1), (2), (3)) val (v)

I'm not particularly irked at this though I was hoping to fix a somewhat
complex query of mine by simply adding a "DISTINCT" to the array_agg that I
am building from derived (using CASE) data.

I am curious to the reason for the limitation, particularly as it would
relate to this specific instance.

Any givers?

Thank!

David J.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Paolo De Michele 2015-10-02 21:02:42 postgresql doesn't start
Previous Message Kevin Grittner 2015-10-02 20:20:56 Re: Broken primary key after backup restore.