Explain Codes LogoExplain Codes Logo

Listagg in Oracle to return distinct values

sql
listagg
distinct-values
oracle-sql
Anton ShumikhinbyAnton Shumikhin·Sep 21, 2024
TLDR

For clear-cut usage of LISTAGG with distinct values in Oracle:

SELECT LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_name) AS distinct_values FROM ( SELECT DISTINCT column_name FROM your_table ) subquery;

This grain of wisdom will make LISTAGG focus only on distinct records, providing a comma-separated aggregation of unique values from your_table.

Further down the rabbit hole of distinct LISTAGG

The fast solution fits the bill for the most clear-cut scenario. But, de Bono's hats aren't black and white only; there are shades, nuances - and so does LISTAGG.

More columns? No problem!

When juggling with multiple columns, you often want distinct combinations:

-- what about a DISTINCT combo? SELECT LISTAGG(column1 || ',' || column2, ';') WITHIN GROUP (ORDER BY column1, column2) AS distinct_combos FROM ( SELECT DISTINCT column1, column2 FROM your_table ) subquery;

Chew before you swallow: Handling large datasets

Oracle can be a picky eater and has a limit on the size of strings it happily ingests from LISTAGG. For large datasets, you might prefer to use the CAST function or slice and dice the data into smaller bites.

May the Performance be with you: testing distinct LISTAGG

Always trial run and tune your LISTAGG implementations as subqueries or additional analytic functions may eat away your execution speed and resource pie. I'd go for the biggest piece, wouldn't you?

Mastering the art of distinct LISTAGG: techniques and alternatives

When life gives you Oracle 19c, make LISTAGG DISTINCT

Oracle 19c brought a cool feature: LISTAGG DISTINCT, making our lives easier, just like a lemon squeezer:

-- when life is kind SELECT LISTAGG(DISTINCT column_name, ',') WITHIN GROUP (ORDER BY column_name) FROM your_table;

Artisan crafting with COLLECT and TABLE functions

Our trusty sidekicks COLLECT and TABLE allow us to gather distinct values and convert them into a clean format for LISTAGG:

-- when we want to show off SELECT LISTAGG(column_value, ',') WITHIN GROUP (ORDER BY column_value) FROM ( SELECT COLUMN_VALUE AS column_value FROM TABLE( CAST(COLLECT(DISTINCT column_name) AS column_name_typ) ) ) subquery;

The window to DISTINCT heaven

It's not always sunshine and rainbows, and ROW_NUMBER() often comes to the rescue for filtering distinct values:

-- "Window" shopping for code SELECT LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_name) AS distinct_values FROM ( SELECT column_name, ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY NULL) AS rn FROM your_table ) WHERE rn = 1;

Sky's the limit? Not with CLOB!

Hitting a VARCHAR limit? Just convert CLOB to VARCHAR for grouping. Like turning water into wine, but SQL-style:

-- Oracle's got a sweet tooth for CLOB SELECT DBMS_LOB.SUBSTR( LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_name), 4000, 1 ) AS distinct_values FROM your_table;