Search code examples
sqlsnowflake-cloud-data-platform

Pick best filled value of multiple occurences of value combination in SQL

I have the following piece of SQL code:

select
   date,
   session_id,
   article_id,
   category
from
   table

The outcome is now something like this:

date session_id article_id category
01-05 124 xyz animals
01-05 124 xyz ""
01-05 124 xyz null
01-05 456 qwert sports
01-05 456 qwert ""
01-05 456 qwert sports

I want to have one row per date, session_id and article_id combination. The category should be the one that is best filled. So in this case there should be one row with 'animals' and one row with 'sports'. If category is only filled with "" or null, then null is fine.

Solution

  • A simple aggregation should work here:

    SELECT date, session_id, article_id, MAX(category) category
FROM yourTable
GROUP BY date, session_id, article_id;

    The MAX() function will ignore nulls across a group. And non zero length strings are "greater" than empty string.