Search code examples
sqlsnowflake-cloud-data-platformlistagg

How to use DESC in a LISTAGG function in Snowflake to organize concatenated data in decreasing order?


I am using following query in Snowflake to get the SKU # and their processing dates using LISTAGG function

SELECT DISTINCT SKU, LISTAGG(DISTINCT Date, ';')
FROM SKU_Data

Result for the query

I want to use the DESC for the LISTAGG function so I can have dates in descending order. I modify the upper query into following

SELECT DISTINCT SKU, LISTAGG(DISTINCT Date, ';' DESC)
FROM SKU_Data

But I having an error.


Solution

  • You need to use WITHIN GROUP (ORDER BY date DESC).

    SELECT
      sku,
      LISTAGG(DISTINCT date, ';') WITHIN GROUP (ORDER BY date DESC) AS date_list
    FROM sku_data
    GROUP BY sku