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

On Sep 15, 9:47*pm, Sashi <small... (AT) gmail (DOT) com> wrote:
Provide some sample data so we can test this ourselves.


David Fitzjarrell

On Sep 16, 7:49*am, ddf <orat... (AT) msn (DOT) com> wrote:
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

On Sep 16, 10:39*am, Sashi <small... (AT) gmail (DOT) com> wrote:
In your sample data is SMITH really a duplicate or should you be
collecting the combination of LNAME||','||FNAME ?

I ask because the multiple SMITH's are not really the same rows though
you may not care.

I just hate to see someone spend effort on solving the wrong issue.

HTH -- Mark D Powell --

"Sashi" <smalladi (AT) gmail (DOT) com> a écrit dans le message de news: 3ee9f014-288a-4aba-bba9-c1828fe8c3db...oglegroups.com...
SQL> create table t (val integer);

Table created.

SQL> insert into t values (1);

1 row created.

SQL> insert into t values (2);

1 row created.

SQL> insert into t values (1);

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select collect(val) from t;
COLLECT(VAL)
-----------------------------------------------
SYSTPpPD1MGj3Qdyoo0Jt2E7Lhg==(1, 2, 1, 1)

1 row selected.

You can use SET operator it is made for this.
Unfornatunatly, at least in 10.2.0.4, it does not directly work
on collection generated by COLLECT.

SQL> select set(collect(val)) from t;
select set(collect(val)) from t
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected UDT got -

You have to create your own same datatype:


SQL> create or replace type mytyp as table of integer;
2 /

Type created.

SQL> select set(cast(collect(val) as mytyp)) from t;
SET(CAST(COLLECT(VAL)ASMYTYP))
-------------------------------------------------------
MYTYP(1, 2)

1 row selected.

Regards
Michel

Sashi <smalladi (AT) gmail (DOT) com> wrote:
select collect(distinct a1)... should work.

--
__________________________________________________ _____________________
Dan Blum tool (AT) panix (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

"Dan Blum" <tool (AT) panix (DOT) com> a écrit dans le message de news: h8rdtj$e66$1 (AT) reader1 (DOT) panix.com...
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

On Sep 16, 2:36*pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
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

ddf <oratune (AT) msn (DOT) com> wrote:
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 (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

ddf wrote:
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