LISTAGG with DISTINCT option

Stew Ashton

Hi Stew

Nice solution by using xmlagg, but why do you prefer regexp_replace over the row_number solution ?

I do not notice much difference in execution time, but I would guess deduplicating before creating the text would be more efficient.

Moreover you must be sure that usage does never contain spaces.

with t as ( 
   select decode( row_number() over (partition by usage order by 1) ,1,usage,null) as usage   from sys.all_identifiers)select trim(',' from    xmlcast(xmlagg(xmlelement(E,','||USAGE) order by USAGE) AS CLOB))as deduppedfrom t;

Regards,
Dirk

Hi Dirk,

Thanks for your comment. I did a test with spaces and I don't see the problem; could you demonstrate?

I'm not sure I "prefer" the solution I provided. I was trying to find a solution that was a function (even with complex expressions in the parameters) and that could be plugged into the query without requiring subqueries or inline views.

If we are allowed subqueries, I would go for SELECT DISTINCT, not row_number. DISTINCT is the obvious and semantically meaningful notion, and I presume uses less memory.

Best regards,

Stew

Stew Ashton

Getting back to the original suggestion, I have a question about the idea itself: what should happen if we say

SELECT LISTAGG(DISTINCT COL1) WITHIN GROUP(ORDER BY COL2) ?

If there are multiple COL1 values, which COL2 should be chosen for ordering?

Best regards,

Stew

Solomon Yakobson

Getting back to the original suggestion, I have a question about the idea itself: what should happen if we say

SELECT LISTAGG(DISTINCT COL1) WITHIN GROUP(ORDER BY COL2) ?

If there are multiple COL1 values, which COL2 should be chosen for ordering?

Best regards,

Stew

Stew,

This is no different from:

SQL> select  distinct ename

  2    from  emp

  3    order by job

  4  /

  order by job

           *

ERROR at line 3:

ORA-01791: not a SELECTed expression

SQL>

SY,

Stew Ashton

Stew,

This is no different from:

SQL> select  distinct ename

  2    from  emp

  3    order by job

  4  /

  order by job

           *

ERROR at line 3:

ORA-01791: not a SELECTed expression

SQL>

SY,

It could be the same or it could be different.

According to this site https://modern-sql.com/feature/listagg

the SQL standard does not say that is an exception.

And the similar COLLECT function doesn't raise an exception either: it just ignores the DISTINCT.

SQL> select cast(collect(distinct job) as sys.odcivarchar2list) jobs  2  from emp;JOBS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               ------------------------------------------------------------------------ODCIVARCHAR2LIST('ANALYST', 'CLERK', 'MANAGER', 'PRESIDENT', 'SALESMAN')SQL> select cast(collect(distinct job order by ename) as sys.odcivarchar2list) jobs  2  from emp;JOBS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               -------------------------------------------------------------------------------------------------------------------------------------------------------------ODCIVARCHAR2LIST('CLERK', 'SALESMAN', 'MANAGER', 'MANAGER', 'ANALYST', 'CLERK', 'MANAGER', 'PRESIDENT', 'SALESMAN', 'CLERK', 'CLERK', 'SALESMAN', 'SALESMAN')

So my question stands, since the answer is not obvious. I do take your reply as a vote for raising an exception: that is a valid possibility. I certainly prefer it to what the COLLECT function does!

Stew

Solomon Yakobson

It could be the same or it could be different.

According to this site https://modern-sql.com/feature/listagg

the SQL standard does not say that is an exception.

And the similar COLLECT function doesn't raise an exception either: it just ignores the DISTINCT.

SQL> select cast(collect(distinct job) as sys.odcivarchar2list) jobs  2  from emp;JOBS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               ------------------------------------------------------------------------ODCIVARCHAR2LIST('ANALYST', 'CLERK', 'MANAGER', 'PRESIDENT', 'SALESMAN')SQL> select cast(collect(distinct job order by ename) as sys.odcivarchar2list) jobs  2  from emp;JOBS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               -------------------------------------------------------------------------------------------------------------------------------------------------------------ODCIVARCHAR2LIST('CLERK', 'SALESMAN', 'MANAGER', 'MANAGER', 'ANALYST', 'CLERK', 'MANAGER', 'PRESIDENT', 'SALESMAN', 'CLERK', 'CLERK', 'SALESMAN', 'SALESMAN')

So my question stands, since the answer is not obvious. I do take your reply as a vote for raising an exception: that is a valid possibility. I certainly prefer it to what the COLLECT function does!

Stew

I see your point but, IMHO, it's a bug. DISTINCT implies ORDER BY list is same or is a subset of DISTINCT expression list expressions (not even column list):

SQL> select  distinct sal + nvl(comm,0)

  2    from  emp

  3    order by sal

  4  /

  order by sal

           *

ERROR at line 3:

ORA-01791: not a SELECTed expression

SQL>

SY.

Racer I.

Hi,

> According to this site https://modern-sql.com/feature/listagg the SQL standard does not say that is an exception.

Arguably it doesn't allow the COLLECT result as it states that all duplicates must still be removed it just doesn't say which copy will survive. COLLECT apparently doesn't remove duplicates that aren't adjacent. Can anyone say if LISTAGG-DISTINCT shows the same behaviour?

regards,

Racer I.

Hi,

I just checked it and LISTAGG-DISTINCT works Ok with non-matching ORDER BY :

https://livesql.oracle.com/apex/livesql/s/hxn75ii2xjqfqhn0c1wxpu2zx

regards,

Racer I.

Hi,

Also arguably with the precedent of LISTAGG-DISTINCT the SELECT case could now behave the same, instead of raising ORA-01791...

regards,