Thanks for the question, Arun.

Asked: February 23, 2017 - 7:13 am UTC

Last updated: April 29, 2020 - 4:08 pm UTC

Version: 11g

Viewed 50K+ times! This question is

You Asked

Hi Guys,

If I use LISTAGG for a single table it gives distance values in that column. But if I use it for by joining multiple tables it gives all the values even though they are duplicating.

Scenario:

CREATE TABLE TEMP1(ID1 NUMBER,TYPE1 CHAR(5));

Insert into TEMP1 (ID1,TYPE1) values (1,'T11 ');
Insert into TEMP1 (ID1,TYPE1) values (1,'T12 ');

CREATE TABLE TEMP2(ID1 NUMBER,TYPE2 CHAR(5));

Insert into TEMP2 (ID1,TYPE2) values (1,'T21 ');
Insert into TEMP2 (ID1,TYPE2) values (1,'T22 ');

Below query gives distinct values in col2 which I'm good with:
SELECT TEMP1.ID1,LISTAGG(TYPE1,',') WITHIN GROUP (ORDER BY TYPE1) FROM TEMP1
GROUP BY TEMP1.ID1;

Below query gives all values in col2 which I don't want, Here I want only distinct values from LISTAGG function.
SELECT distinct TEMP1.ID1,LISTAGG(TYPE1,',') WITHIN GROUP (ORDER BY TYPE1) FROM TEMP1,TEMP2
WHERE TEMP1.ID1 = TEMP2.ID1
GROUP BY TEMP1.ID1;

Thanks!

and Connor said...

In the current versions of Oracle, LISTAGG does not support DISTINCT so you need to take care of the distinct values yourself before applying listagg.

Here's an example of that


SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t (
  2    x int,
  3    v varchar2(10)
  4    );

Table created.

--
--  x=1 will be good stuff, x=2 will be bad stuff
--

SQL>
SQL> insert into t values (1,'John');

1 row created.

SQL> insert into t values (1,'Sue');

1 row created.

SQL> insert into t values (1,'Mark');

1 row created.

SQL>
SQL>
SQL> insert into t values (2,'John');

1 row created.

SQL> insert into t values (2,'Sue');

1 row created.

SQL> insert into t values (2,'John');

1 row created.

SQL> insert into t values (2,'Sue');

1 row created.

SQL> insert into t values (2,'Jane');

1 row created.

SQL>
SQL> select listagg(v,',') within group ( order by v )
  2  from t
  3  where x = 1;

LISTAGG(V,',')WITHINGROUP(ORDERBYV)
----------------------------------------------------------------------------------------------------------------------------------
John,Mark,Sue

1 row selected.

SQL>
SQL>
SQL> select listagg(v,',') within group ( order by v )
  2  from t
  3  where x = 2;

LISTAGG(V,',')WITHINGROUP(ORDERBYV)
----------------------------------------------------------------------------------------------------------------------------------
Jane,John,John,Sue,Sue

1 row selected.

SQL>
SQL> select listagg(v,',') within group ( order by v )
  2  from ( select distinct v from t where x = 2 );

LISTAGG(V,',')WITHINGROUP(ORDERBYV)
----------------------------------------------------------------------------------------------------------------------------------
Jane,John,Sue

1 row selected.

SQL>

Rating

(8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, February 23, 2017 - 12:46 pm UTC

Is it possible with unpivot keyword or not? If it it possible please share here?

February 25, 2017 - 12:50 am UTC

Not sure what you mean.

Semi Joins

Rajeshwaran, Jeyabal, February 23, 2017 - 2:16 pm UTC

Given this query.

select distinct TEMP1.ID1,LISTAGG(TYPE1,',') within group (order by TYPE1)  x
from TEMP1,TEMP2 
where TEMP1.ID1 = TEMP2.ID1 
group by TEMP1.ID1;

we use TEMP2 only for joins, no data retrieval from it, so you don't an equi join, instead it could be a Semi-join to suppress the duplicates.

demo@ORA11G> column x format a30
demo@ORA11G> select distinct TEMP1.ID1,LISTAGG(TYPE1,',') within group (order by TYPE1)  x
  2  from TEMP1,TEMP2
  3  where TEMP1.ID1 = TEMP2.ID1
  4  group by TEMP1.ID1;

       ID1 X
---------- ------------------------------
         1 T11  ,T11  ,T12  ,T12

demo@ORA11G> select temp1.id1,listagg(temp1.type1,',') within group (order by temp1.type1) x
  2  from temp1
  3  where temp1.id1 in ( select temp2.id1 from temp2 )
  4  group by temp1.id1;

       ID1 X
---------- ------------------------------
         1 T11  ,T12

demo@ORA11G>

Another approach for DISTINCT LISTAGG

Eugen Iacob, June 11, 2018 - 2:20 pm UTC

--drop function listagg_distinct;
--drop type LISTAGG_TYPE;
--drop type array_type;
--drop table LISTAGG_TABLE_DEMO;

create type array_type as table of varchar2(100);
/


create or replace TYPE LISTAGG_TYPE AS OBJECT
(
  test array_type,
  MEMBER PROCEDURE MERGE(test IN OUT array_type, value VARCHAR2),
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT LISTAGG_TYPE) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT LISTAGG_TYPE, VALUE IN VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateTerminate(self IN LISTAGG_TYPE, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT LISTAGG_TYPE, ctx2 IN LISTAGG_TYPE) RETURN NUMBER
);
/

create or replace TYPE BODY LISTAGG_TYPE
IS
  MEMBER PROCEDURE MERGE(test IN OUT array_type, value VARCHAR2) IS
  BEGIN
    DECLARE 
      v_found boolean:=false; v_insert_at pls_integer:=0; 
    BEGIN
      IF test.count=0 THEN
        test.extend;
        test(1):=value;
      ELSE
        FOR i IN 1..test.count LOOP
           CASE
             WHEN value=test(i) THEN
               v_found:=true;
               EXIT WHEN 1=1;
             WHEN (i < test.count and test(i) < value and value < test(i+1)) or (i = test.count and test(i) < value) THEN
               v_insert_at:=i+1;
               EXIT WHEN 1=1;
             ELSE
               NULL;
           END CASE;
        END LOOP;
        IF v_found THEN
          NULL;
        ELSIF not v_found and v_insert_at=test.count+1 THEN
           test.extend;
           test(test.count):=value;
        ELSIF not v_found and v_insert_at!=0 THEN
          test.extend;
          FOR i IN REVERSE v_insert_at..test.count-1 LOOP
            test(i+1):=test(i);
          END LOOP;
          test(v_insert_at):=value;
        ELSIF not v_found and v_insert_at=0 THEN
          test.extend;
          FOR i IN REVERSE 2..test.count LOOP
            test(i):=test(i-1);
          END LOOP;
          test(1):=value;
        END IF;
      END IF;
    END;
  END;
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT LISTAGG_TYPE) RETURN NUMBER IS
    BEGIN
      sctx := LISTAGG_TYPE (array_type());
      RETURN ODCIConst.Success;
    END;
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT LISTAGG_TYPE, VALUE IN VARCHAR2) RETURN NUMBER IS
    BEGIN
      MERGE(self.test, value);
      RETURN ODCIConst.Success;
    END;
  MEMBER FUNCTION ODCIAggregateTerminate(self IN LISTAGG_TYPE, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER IS
    BEGIN DECLARE v_tmp VARCHAR2(4000); BEGIN
      FOR i in 1..self.test.count LOOP
        v_tmp:=v_tmp||self.test(i)||case when i!=self.test.count then ',' end;
      END LOOP;
      returnValue := v_tmp;
      RETURN ODCIConst.Success;
    END; END;
  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT LISTAGG_TYPE, ctx2 IN LISTAGG_TYPE) RETURN NUMBER IS
    BEGIN
      FOR i in 1..ctx2.test.count LOOP
        MERGE(self.test, ctx2.test(i));
      END LOOP;
      RETURN ODCIConst.Success;
    END;
END;
/   

create or replace FUNCTION listagg_distinct(input VARCHAR2) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING LISTAGG_TYPE;
/

create table LISTAGG_TABLE_DEMO (player_name varchar2(20), faults number(10,0));
INSERT  INTO listagg_table_demo (player_name, faults)
  SELECT 'Player ' || ( trunc(level / 100) + 1 ), trunc(level / 10) + 1 faults FROM dual CONNECT BY level <= POWER(10,3);
commit;

select player_name, listagg_distinct(faults) distinct_values, listagg(faults, ',') within group (order by faults) listagg_built_in from LISTAGG_TABLE_DEMO group by player_name;

How 'bout listagg_distinct with overflow truncate?

Steffan Cline, April 29, 2020 - 1:45 am UTC

I like Eugen Iacob's approach. Wish there was a way to include the new overflow truncate into this so we'd have a Swiss Army knife for the older Oracle versions.

April 29, 2020 - 2:51 am UTC

I've got a LISTAGG_CLOB function here

https://github.com/connormcd/listagg_clob

Have a crack at merging that with Eugen's distinct handling.

optimizing search

Mikhail Velikikh, April 29, 2020 - 11:45 am UTC

Eugen provided an excellent piece of code. I would like to suggest a small improvement based on my previous experience.
I fought tooth and nail to optimize a similar function on one project I was working on.

Provided that the table is created as follows:

create table LISTAGG_TABLE_DEMO (player_name varchar2(20), faults number(10,0));
INSERT  INTO listagg_table_demo (player_name, faults)
  SELECT 'Player ' || player_id, fault_id faults 
    from
  (select level player_id from dual connect by level<=10),
  (select level fault_id from dual connect by level<=100),
  (select level duplicate_id from dual connect by level<=1000);
commit;

select player_name, listagg_distinct(faults) distinct_values
  from LISTAGG_TABLE_DEMO
 group by player_name;

The last query takes 16 seconds on my instance.
I ran it through PL/SQL Hierarchical profiler and here are the results:
The query itself took: 17.9 seconds
LISTAGG_TYPE.ODCIAGGREGATEITERATE: 12.5 seconds (self) + 1.7 seconds (descendants) = 14.2 seconds in total (~80% of the total runtime).

The part that can be improved, is this:

        FOR i IN 1..test.count LOOP

Essentially, its runtime cost is O(N) where 'N' is the cardinality of 'test'. We do it for every row (ODCIAGGREGATEITERATE is called 1M times on this data per DBMS_HPROF output).
I would try to improve that search if I wanted to make it faster.
Here is the idea: what if we use an associative array instead. Based on: https://docs.oracle.com/en/database/oracle/oracle-database/20/lnpls/plsql-collections-and-records.html#GUID-8060F01F-B53B-48D4-9239-7EA8461C2170

> The data type of index can be either a string type (VARCHAR2, VARCHAR, STRING, or LONG) or PLS_INTEGER. Indexes are stored in sort order, not creation order. For string types, sort order is determined by the initialization parameters NLS_SORT and NLS_COMP.

Thus, its data is already sorted, so we do not need to find the index where to insert a new key (the v_insert_at variable in the beautiful Eugen's code).
The following implementation is used just as a demonstration only (it is a partial implementation to show the associative array usage):

doc
  alternative implementation without sorting. I just wrote this package from scratch.
#
create or replace package listagg_aux
is
  subtype key_type is varchar2(100);
  procedure init_map;
  procedure free_map;
  procedure put(key key_type);
  function to_string(separator char := ',') return varchar2;
end;
/
create or replace package body listagg_aux
is
  type key_map_tbl_type is table of boolean index by key_type;
  empty_map key_map_tbl_type;
  key_map key_map_tbl_type;
  procedure init_map
  is
  begin
    key_map := empty_map;
  end init_map;
  procedure free_map
  is
  begin
    key_map := empty_map;
  end free_map;
  procedure put(key key_type)
  is
  begin
    if not key_map.exists(key)
    then
      key_map(key) := true;
    end if;
  end put;
  function to_string(separator char := ',') return varchar2
  is
    return_value varchar2(32767);
    key_index key_type := key_map.first;
  begin
    while key_index is not null
    loop
      if return_value is null
      then
        return_value := key_index;
      else
        return_value := return_value || separator || key_index;
      end if;
      key_index := key_map.next(key_index);
    end loop;
    return return_value;
  end to_string;
end;
/

create or replace TYPE BODY LISTAGG_TYPE
IS
  MEMBER PROCEDURE MERGE(test IN OUT array_type, value VARCHAR2) IS
    BEGIN
      listagg_aux.put(value);
    END;
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT LISTAGG_TYPE) RETURN NUMBER IS
    BEGIN
      sctx := LISTAGG_TYPE (array_type());
      listagg_aux.init_map();
      RETURN ODCIConst.Success;
    END;
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT LISTAGG_TYPE, VALUE IN VARCHAR2) RETURN NUMBER IS
    BEGIN
      MERGE(self.test, value);
      RETURN ODCIConst.Success;
    END;
  MEMBER FUNCTION ODCIAggregateTerminate(self IN LISTAGG_TYPE, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER IS
    BEGIN 
      returnValue := listagg_aux.to_string();
      listagg_aux.free_map();
      RETURN ODCIConst.Success;
    END; 
  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT LISTAGG_TYPE, ctx2 IN LISTAGG_TYPE) RETURN NUMBER IS
    BEGIN
        raise program_error;
    END;
END;
/   

select player_name, listagg_distinct(faults) distinct_values
  from LISTAGG_TABLE_DEMO
 group by player_name;

The same query takes <5 seconds after that modification.
The DBMS_HPROF output as follows:
The query itself: 5.4 seconds
LISTAGG_TYPE.ODCIAGGREGATEITERATE: 1 second (self) + 0.6 seconds (descendants) = 1.6 seconds in total (~30% of the total runtime)

The things that I would improve with that approach:
1. LISTAGG_AUX is a package to keep that associative array.
It would be nice if non-persistable types supported those arrays as well. I think I tested it a while ago and it was not supported.
2. LISTAGG_AUX.KEY_MAP is session based.
Although it works in this specific example, I think it may become an issue in more complex cases (what if we need to do LISTAGG_DISTINCT for several columns in one query?).
If that happens to be an issue, I would introduce an instance identifier at the ODCIAggregateInitialize. We can just generate SYS_GUID and use it as an instance identifier passing it to all LISTAGG_AUX calls.

April 29, 2020 - 12:23 pm UTC

Or if you're on 18c or higher, you can get a DISTINCT CSV as a CLOB by:

with grps as (
 select player_name, 
       json_arrayagg ( distinct faults returning clob ) faults
 from LISTAGG_TABLE_DEMO
 group by player_name
)
  select player_name, substr ( faults, 2, length ( faults ) - 2 ) from grps;

;)

Nice work though; handy for people stuck on earlier releases.

json_arrayagg distinct?

Mikhail Velikikh, April 29, 2020 - 1:58 pm UTC

There are duplicates in the output on 19.7 and livesql.oracle.com with the proposed JSON_ARRAYAGG solution:

with listagg_table_demo(player_name, faults) as (
  select 'p1', 1 from dual union all
  select 'p1', 1 from dual union all
  select 'p1', 2 from dual
),
     grps as (
 select player_name, 
       json_arrayagg ( distinct faults returning clob ) faults
 from LISTAGG_TABLE_DEMO
 group by player_name
)
  select player_name, substr ( faults, 2, length ( faults ) - 2 ) agg_faults from grps;

SQL> with listagg_table_demo(player_name, faults) as (
  2    select 'p1', 1 from dual union all
  3    select 'p1', 1 from dual union all
  4    select 'p1', 2 from dual
  5  ),
  6       grps as (
  7   select player_name,
  8         json_arrayagg ( distinct faults returning clob ) faults
  9   from LISTAGG_TABLE_DEMO
 10   group by player_name
 11  )
 12    select player_name, substr ( faults, 2, length ( faults ) - 2 ) agg_faults from grps;

PL AGG_FAULTS
-- ------------------------------
p1 1,2,1

I do not actually see that JSON_ARRAYAGG supports DISTINCT at all even on 20c: https://docs.oracle.com/en/database/oracle/oracle-database/20/sqlrf/JSON_ARRAYAGG.html#GUID-6D56077D-78DE-4CC0-9498-225DDC42E054
Thus, there might be a bug and the syntax error should be thrown instead of that query returning any results.

April 29, 2020 - 4:08 pm UTC

Ah yes, I should've looked closer... My mistake.

Many aggregate functions do support the DISTINCT keyword; I'll follow up to see whether this is intended for JSON_ARRAYAGG or not.

Amazing performance!

Steffan Cline, April 30, 2020 - 6:25 pm UTC

Chris Saxon, I'm good with SQL but a novice with PL/SQL. I'll give it a shot. I think I used an earlier version of yours to get past overflow errors. I didn't know it supported the options found in later versions of Oracle.

Mikhail Velikikh, your additions are interesting but still over my head a bit.

I'll be reading up on this more as I strive to get better with PL/SQL. I tested a direct copy of Eugen's post yesterday and although I wasn't able to use the cool optimizers you have, I only saw a minimal impact in speed. I was quite amazed at how fast it ran with no overflow errors.

Anyone know if this too with Eugen, Chris and Mikhail's enhancements is on GitHub somewhere all merged together? I'd love to beta test it for you all. ;-)

on Distinct LISTAGG

Rajeshwaran, Jeyabal, May 01, 2020 - 8:31 am UTC

on the existing stragg function from this link ( @ https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2196162600402 )

just modified the ODCIAggregateTerminate like this

  member function odciaggregateterminate(self in stragg_agg_type,
    returnvalue out  varchar2,
    flags in number) 
  return number as 
  begin
  for x in (select distinct column_value from table(self.l_total) order by 1)
  loop
   returnvalue := returnvalue ||';'||x.column_value;
  end loop;
   returnvalue := trim(';' from returnvalue);
   return odciconst.success;
  end;

Post that it produces the result set like this-with duplicates eliminated.

demo@PDB1> col job_lists format a80
demo@PDB1> select player_name, stragg( faults ) job_lists
  2  from LISTAGG_TABLE_DEMO
  3  group by player_name
  4  order by player_name
  5  fetch first 3 rows only
  6  /

PLAYER_NAME          JOB_LISTS
-------------------- --------------------------------------------------------------------------------
Player 1             1;10;100;11;12;13;14;15;16;17;18;19;2;20;21;22;23;24;25;26;27;28;29;3;30;31;32;3
                     3;34;35;36;37;38;39;4;40;41;42;43;44;45;46;47;48;49;5;50;51;52;53;54;55;56;57;58
                     ;59;6;60;61;62;63;64;65;66;67;68;69;7;70;71;72;73;74;75;76;77;78;79;8;80;81;82;8
                     3;84;85;86;87;88;89;9;90;91;92;93;94;95;96;97;98;99

Player 10            1;10;100;11;12;13;14;15;16;17;18;19;2;20;21;22;23;24;25;26;27;28;29;3;30;31;32;3
                     3;34;35;36;37;38;39;4;40;41;42;43;44;45;46;47;48;49;5;50;51;52;53;54;55;56;57;58
                     ;59;6;60;61;62;63;64;65;66;67;68;69;7;70;71;72;73;74;75;76;77;78;79;8;80;81;82;8
                     3;84;85;86;87;88;89;9;90;91;92;93;94;95;96;97;98;99

Player 2             1;10;100;11;12;13;14;15;16;17;18;19;2;20;21;22;23;24;25;26;27;28;29;3;30;31;32;3
                     3;34;35;36;37;38;39;4;40;41;42;43;44;45;46;47;48;49;5;50;51;52;53;54;55;56;57;58
                     ;59;6;60;61;62;63;64;65;66;67;68;69;7;70;71;72;73;74;75;76;77;78;79;8;80;81;82;8
                     3;84;85;86;87;88;89;9;90;91;92;93;94;95;96;97;98;99

with 19c - distinct supported on LISTAGG - using that it went like this

c##rajesh@PDB19c> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

c##rajesh@PDB19c> col job_lists format a80
c##rajesh@PDB19c> select player_name, listagg( distinct faults ,',')within group(order by faults) job_lists
  2  from LISTAGG_TABLE_DEMO
  3  group by player_name
  4  order by player_name
  5  fetch first 3 rows only;

PLAYER_NAME          JOB_LISTS
-------------------- --------------------------------------------------------------------------------
Player 1             1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
                     ,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,5
                     7,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,
                     84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100

Player 10            1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
                     ,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,5
                     7,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,
                     84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100

Player 2             1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
                     ,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,5
                     7,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,
                     84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100


c##rajesh@PDB19c>

or even we could do distinct + listagg on prior to 19c - something like this.

demo@PDB1> select player_name, listagg( faults , ';') within group (order by faults) job_lists
  2  from (
  3  select distinct player_name, faults
  4  from LISTAGG_TABLE_DEMO
  5      )
  6  group by player_name
  7  order by player_name
  8  fetch first 3 rows only;

PLAYER_NAME          JOB_LISTS
-------------------- --------------------------------------------------------------------------------
Player 1             1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30
                     ;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;5
                     7;58;59;60;61;62;63;64;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;
                     84;85;86;87;88;89;90;91;92;93;94;95;96;97;98;99;100

Player 10            1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30
                     ;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;5
                     7;58;59;60;61;62;63;64;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;
                     84;85;86;87;88;89;90;91;92;93;94;95;96;97;98;99;100

Player 2             1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30
                     ;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;5
                     7;58;59;60;61;62;63;64;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;
                     84;85;86;87;88;89;90;91;92;93;94;95;96;97;98;99;100

Live SQL

Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!

Dev Gym

Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Semi Joins

Another approach for DISTINCT LISTAGG

How 'bout listagg_distinct with overflow truncate?

optimizing search

json_arrayagg distinct?

Amazing performance!

on Distinct LISTAGG

More to Explore

Live SQL

Dev Gym

LISTAGG not giving distinct values

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Semi Joins

Another approach for DISTINCT LISTAGG

How 'bout listagg_distinct with overflow truncate?

optimizing search

json_arrayagg distinct?

Amazing performance!

on Distinct LISTAGG

More to Explore

Live SQL

Dev Gym