Forum Stats

  • 3,816,052 Users
  • 2,259,134 Discussions
  • 7,893,371 Comments

Discussions

LISTAGG with DISTINCT option

1235»

Comments

  • Stew Ashton
    Stew Ashton Member Posts: 2,894 Bronze Crown

    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
    Stew Ashton Member Posts: 2,894 Bronze Crown

    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
    Solomon Yakobson Member Posts: 19,329 Red Diamond

    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 Ashton Member Posts: 2,894 Bronze Crown
    edited Jun 4, 2018 8:29AM

    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
    Solomon Yakobson Member Posts: 19,329 Red Diamond
    edited Jun 4, 2018 9:09AM

    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.
    Racer I. Member Posts: 113

    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.
    Racer I. Member Posts: 113

    Hi,

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

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

    regards,

    ApexBine
  • Racer I.
    Racer I. Member Posts: 113

    Hi,

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

    regards,