Re: Collect function

From: Dan Blum <tool_at_panix.com>
Date: Wed, 16 Sep 2009 20:24:11 +0000 (UTC)
Message-ID: <h8rhha$1gc$1_at_reader1.panix.com>

ddf <oratune_at_msn.com> wrote:
> On Sep 16, 2:36?pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> > "Dan Blum" <t..._at_panix.com> a ?crit dans le message de news: h8rdtj$e6..._at_reader1.panix.com...| Sashi <small..._at_gmail.com> wrote:
> >
> > | > HI all, I'm trying to use the collect() function (mentioned in my
> > | > earlier post in this ng) but it, well, collects all values. Is there
> > | > any way of avoiding duplicates?
> > | > What I'm trying to do is this:
> > |
> > | > select collect(a1), a2, a3, a4
> > | > from A
> > | > group by a2, a3, a4.
> > |
> > | select collect(distinct a1)... should work.
> > |
> > | --
> > | _______________________________________________________________________
> > | Dan Blum ? ? ? ? ?t..._at_panix.com
> > | "I wouldn't have believed it myself if I hadn't just made it up."
> >
> > It does.
> >
> > SQL> select collect(val) from t;
> > COLLECT(VAL)
> > ---------------------------------------------
> > SYSTPYmvHKMavRw27KIB3yptjFg==(1, 2, 1, 1)
> >
> > 1 row selected.
> >
> > SQL> select collect(distinct val) from t;
> > COLLECT(DISTINCTVAL)
> > ---------------------------------------------
> > SYSTPYmvHKMavRw27KIB3yptjFg==(1, 2)
> >
> > 1 row selected.
> >
> > Regards
> > Michel

> But not with a GROUP BY query:

Weird. DISTINCT works inside aggregation functions (SELECT COUNT(DISTINCT) GROUP BY works, for example), so one would expect it to work here.

-- 
_______________________________________________________________________
Dan Blum					         tool_at_panix.com	
"I wouldn't have believed it myself if I hadn't just made it up."

Received on Wed Sep 16 2009 - 15:24:11 CDT