Re: Collect function

From: Sashi <smalladi_at_gmail.com>
Date: Wed, 16 Sep 2009 07:39:13 -0700 (PDT)
Message-ID: <5dfee9af-c900-4972-88d9-ef8739ca36bf_at_g1g2000vbr.googlegroups.com>

On Sep 16, 7:49 am, ddf <orat..._at_msn.com> wrote:
> On Sep 15, 9:47 pm, 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.
>
> > Thanks,
> > Sashi
>
> Provide some sample data so we can test this ourselves.
>
> David Fitzjarrell

DDL:
create table employee(dept_id varchar(4), fname varchar(20), lname varchar(20));

DML:
insert into employee(dept_id, fname, lname) values ('1', 'John', 'Smith');
insert into employee(dept_id, fname, lname) values ('1', 'Jane', 'Smith');
insert into employee(dept_id, fname, lname) values ('1', 'Harry', 'Arnold');
insert into employee(dept_id, fname, lname) values ('2', 'Sam', 'Smith');
insert into employee(dept_id, fname, lname) values ('2', 'Samantha', 'Smith');
insert into employee(dept_id, fname, lname) values ('2', 'Peter', 'Jones');

Fetch:
select dept_id, collect(lname) from employee group by dept_id

Result:

DEPT, COLLECT(LNAME)

---

1, SYSTPc7POHe3EMMDgRAAAvqlgUw==('Smith', 'Smith', 'Arnold')

2, SYSTPc7POHe3EMMDgRAAAvqlgUw==('Smith', 'Smith', 'Jones')

The collect function 'collects' requested values and returns it as a collection. It, however, does store dups. I'd like to avoid the dups.

Googling around didn't help much (or I did a bad job of it).

Does anyone know how to restrict the collection to distinct values? collect(distinct lname) doesn't help.

TIA,
Sashi Received on Wed Sep 16 2009 - 09:39:13 CDT