Re: Collect function
Date: Wed, 16 Sep 2009 13:00:37 -0700 (PDT)
Message-ID: <c037c2fd-108d-490c-bf0e-221020e1598f_at_m20g2000vbp.googlegroups.com>
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:
SQL> create table employee(dept_id varchar(4), fname varchar(20),
lname
2 varchar(20));
Table created.
SQL>
SQL> insert into employee(dept_id, fname, lname) values ('1', 'John',
2 'Smith');
1 row created.
SQL> insert into employee(dept_id, fname, lname) values ('1', 'Jane', 2 'Smith');
1 row created.
SQL> insert into employee(dept_id, fname, lname) values ('1', 'Harry', 2 'Arnold');
1 row created.
SQL> insert into employee(dept_id, fname, lname) values ('2', 'Sam', 2 'Smith');
1 row created.
SQL> insert into employee(dept_id, fname, lname) values ('2',
'Samantha',
2 'Smith');
1 row created.
SQL> insert into employee(dept_id, fname, lname) values ('2', 'Peter', 2 'Jones');
1 row created.
SQL> SQL> SQL> select dept_id, collect(lname) from employee group by dept_id ;
DEPT
COLLECT(LNAME)
1
SYSTPdq33znlnSueX+6EjD+ARrA==('Smith', 'Smith', 'Arnold')
2
SYSTPdq33znlnSueX+6EjD+ARrA==('Smith', 'Smith', 'Jones')
SQL> select dept_id, collect(distinct lname) from employee group by dept_id ;
DEPT
COLLECT(DISTINCTLNAME)
1
SYSTPdq33znlnSueX+6EjD+ARrA==('Smith', 'Smith', 'Arnold')
2
SYSTPdq33znlnSueX+6EjD+ARrA==('Smith', 'Smith', 'Jones')
SQL> which is what the OP wanted. Michel's solution is probably the best for this situation:
SQL> create or replace type mytype as table of varchar2(30); 2 /
Type created.
SQL>
SQL> select dept_id, set(cast(collect(lname) as mytype)) from employee
group by dept_id ;
DEPT
SET(CAST(COLLECT(LNAME)ASMYTYPE))
1
MYTYPE('Smith', 'Arnold')
2
MYTYPE('Smith', 'Jones')
SQL> David Fitzjarrell Received on Wed Sep 16 2009 - 15:00:37 CDT