Distinct Listagg That Is Inside a Subquery in the Select List

Distinct LISTAGG that is inside a subquery in the SELECT list

The following method gets rid of the in-line view to fetch duplicates, it uses REGEXP_REPLACE and RTRIM on the LISTAGG function to get the distinct result set in the aggregated list. Thus, it won't do more than one scan.

Adding this piece to your code,

RTRIM(REGEXP_REPLACE(listagg (tm_redir.team_code, ',') 
                     WITHIN GROUP (ORDER BY tm_redir.team_code),
                     '([^,]+)(,\1)+', '\1'),
                     ',')

Modified query-

SQL> with tran_party as -- ALL DUMMY DATA ARE IN THESE CTE FOR YOUR REFERENCE
  2           (select 1 tran_party_id, 11 transaction_id, 101 team_id_redirect
  3              from dual
  4            union all
  5            select 2, 11, 101 from dual
  6            union all
  7            select 3, 11, 102 from dual
  8            union all
  9            select 4, 12, 103 from dual
 10            union all
 11            select 5, 12, 103 from dual
 12            union all
 13            select 6, 12, 104 from dual
 14            union all
 15            select 7, 13, 104 from dual
 16            union all
 17            select 8, 13, 105 from dual),
 18       tran as
 19           (select 11 transaction_id, 1001 account_id, 1034.93 amount from dual
 20            union all
 21            select 12, 1001, 2321.89 from dual
 22            union all
 23            select 13, 1002, 3201.47 from dual),
 24       account as
 25           (select 1001 account_id, 111 team_id from dual
 26            union all
 27            select 1002, 112 from dual),
 28       team as
 29           (select 101 team_id, 'UUU' as team_code from dual
 30            union all
 31            select 102, 'VV' from dual
 32            union all
 33            select 103, 'WWW' from dual
 34            union all
 35            select 104, 'XXXXX' from dual
 36            union all
 37            select 105, 'Z' from dual)
 38  -- The Actual Query
 39  select a.account_id,
 40         t.transaction_id,
 41         (SELECT  RTRIM(
 42           REGEXP_REPLACE(listagg (tm_redir.team_code, ',')
 43                     WITHIN GROUP (ORDER BY tm_redir.team_code),
 44             '([^,]+)(,\1)+', '\1'),
 45           ',')
 46            from tran_party tp_redir
 47                 inner join team tm_redir
 48                     on tp_redir.team_id_redirect = tm_redir.team_id
 49                 inner join tran t_redir
 50                     on tp_redir.transaction_id = t_redir.transaction_id
 51           where     t_redir.account_id = a.account_id
 52                 and t_redir.transaction_id != t.transaction_id)
 53             AS teams_redirected
 54    from tran t inner join account a on t.account_id = a.account_id
 55  /

ACCOUNT_ID TRANSACTION_ID TEAMS_REDIRECTED
---------- -------------- --------------------
      1001             11 WWW,XXXXX
      1001             12 UUU,VV
      1002             13

SQL>

oracle correlated subquery using distinct listagg

You can apply the distinct in a subquery, which also has the join - avoiding the level issue:

SELECT ID
     , DESCRIPTION
     , LISTAGG (WIDGET_TYPE, ', ')
         WITHIN GROUP (ORDER BY WIDGET_TYPE) AS widget_types
FROM (
  SELECT DISTINCT A.ID, A.DESCRIPTION, B.WIDGET_TYPE
  FROM TEST A
  JOIN TEST_WIDGET B
  ON B.TEST_FK = A.ID
)
GROUP BY ID, DESCRIPTION
ORDER BY ID;

        ID DESCRIPTION          WIDGET_TYPES       
---------- -------------------- --------------------
         1 cog                  A, B                
         2 wheel                B, C                
         3 spring               A, B, C             

Distinct values in listagg without nested query

RLOG's answer inspired me to "solve" this myself. This is possibly inefficient, but that doesn't matter for my use-case.

Instead of filtering in the query 2nd nested query, I get everything and then filter the result in the 1st nested query.

SELECT tbl_c.bar,
(
    WITH all_names AS (
        SELECT DISTINCT tbl_a.name
        FROM tbl_a
        INNER JOIN tbl_b ON tbl_a.foo = tbl_b.foo
    )
    SELECT LISTAGG(name, ';  ') WITHIN GROUP (ORDER BY name)
    FROM all_names
    WHERE tbl_a.id = tbl_c.bar
) as names
FROM tbl_c;

How do I access a LISTAGG that is used in sub query?

Use the column alias. Since you have used a quoted identifier in the sub-query then you need to use the quoted identifier in the outer query (and anywhere else you may want to reference it):

SELECT inner_query.item_id_alias,
       inner_query."ship_codes"
FROM (
  SELECT li.item_id as item_id_alias,
         LISTAGG(a.ship_code, '; ')
           WITHIN GROUP (ORDER BY a.ship_code) as "ship_codes"
  FROM (
         SELECT DISTINCT
                ship_code, 
                MAX(order_id) as order_id
         FROM   orders o
         WHERE  o.order_id in (
                  SELECT li2.order_id
                  FROM   line_items li2 
                  GROUP BY li2.order_id
                ) 
         GROUP BY ship_code
       )a
       INNER JOIN line_items li ON a.order_id = li.order_id      
  GROUP BY li.item_id
) inner_query;

Alternatively, you can use a wildcard:

SELECT *
FROM   (...) inner_query

or a wildcard with the alias for the sub-query:

SELECT inner_query.*
FROM   (...) inner_query

or the identifiers without the alias for the sub-query:

SELECT item_id_alias,
       "ship_codes"
FROM   (...) inner_query

(Note: using DISTINCT and GROUP BY in the same SELECT statement is pointless. You can remove the DISTINCT and the output will not change.)

listagg in oracle sql

Oracle does not support distinct for listagg() prior to Oracle 19.

In your case, though, the problem is presumably caused by a Cartesian product as a result of the joins. That suggests that correlated subqueries are a better approach:

select t.*,
       (select listagg(a.name, ',') within group (order by a.id)
        from A a
        where a.id = t.id 
       ) as a,
       (select listagg(b.name, ',') within group (order by b.id)
        from B b
        where b.id = t.id 
       ) as b
from some_table t
where t.status in (1, 2, 3)
group by t.id;

Count SELECT DISTINCT LISTAGG

You can use COUNT( DISTINCT ... ) inside the sub-query:

SELECT your_outer_values,
       ( SELECT COUNT( DISTINCT HANDLING_UNIT_ID )
         FROM   SHIPMENT_LINE_HANDL_UNIT
         WHERE  ORDER_NO = SL.ORDER_NO ) AS quantity
FROM   your_table sl

Can anyone advice why this won't work?

There is no GROUP BY clause so LISTAGG will work over the entire results set and aggregate it into a single row consisting of a delimited string list of all the HANDLING_UNIT_ID values. Since there is a single value from LISTAGG then the DISTINCT clause is redundant and the COUNT from the outer query will always return 1.

---

Related Topics