Collect function
in [Oracle Help]
|
From: Dan Blum on 16 Sep 2009 15:22 Sashi <smalladi(a)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 tool(a)panix.com "I wouldn't have believed it myself if I hadn't just made it up."
From: Michel Cadot on 16 Sep 2009 15:36 "Dan Blum" <tool(a)panix.com> a �crit dans le message de news: h8rdtj$e66$1(a)reader1.panix.com... | Sashi <smalladi(a)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 tool(a)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
From: ddf on 16 Sep 2009 16:00 On Sep 16, 2:36 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote: > "Dan Blum" <t...(a)panix.com> a écrit dans le message de news: h8rdtj$e6....(a)reader1.panix.com...| Sashi <small...(a)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...(a)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
From: Dan Blum on 16 Sep 2009 16:24 ddf <oratune(a)msn.com> wrote: > On Sep 16, 2:36?pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote: > > "Dan Blum" <t...(a)panix.com> a ?crit dans le message de news: h8rdtj$e6...(a)reader1.panix.com...| Sashi <small...(a)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...(a)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(a)panix.com "I wouldn't have believed it myself if I hadn't just made it up."
From: Maxim Demenko on 16 Sep 2009 16:32 ddf wrote: > On Sep 16, 2:36 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote: >> "Dan Blum" <t...(a)panix.com> a �crit dans le message de news: h8rdtj$e6...(a)reader1.panix.com...| Sashi <small...(a)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...(a)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: Seems to be fixed in 11gR2 (don't have 11gR1 by hand now) For 10gR2 this (in my opinion buggy) behaviour can be workarounded ( besides using sql types) using an inline view returning distinct set of rows Best regards Maxim
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: Oracle9i Client - where is it ? .... Next: Using a case within a simple update |