SQL - select only one value from multiple rows under certain condition

user_17457816 0
Viewed 77


Hi guys, I am trying to solve the problem since hours researching in the internet and couldn't find any solution.
I have a table like:

Number Language Name
1111 D German name for 1111
1111 E English name for 1111
1111 S Spanish name for 1111
2222 D German name for 2222
3333 S Spanish name for 3333

The logic should be, for each number:

  • Return english name, if Language = E is available.
  • If E is not available, D is available, return German name.
  • If E and D not available, return "name not available"

So the result table should like:

Number Name
1111 English name for 1111
2222 German name for 2222
3333 name not available
snowflake-cloud-data-platform sql
1 Answers

Using ARRAY_AGG:

WITH cte(Number, LANGUAGE, Name) AS (
  SELECT 1111,    'D',    'German name for 1111' UNION ALL
  SELECT 1111,    'E',    'English name for 1111' UNION ALL
  SELECT 1111,    'S',    'Spanish name for 1111' UNION ALL
  SELECT 2222,    'D',    'German name for 2222' UNION ALL
  SELECT 3333,    'S',    'Spanish name for 3333'
)
SELECT Number, 
    (ARRAY_AGG(IFF(LANGUAGE IN ('D','E'), NAME, 'name not available')) 
     WITHIN GROUP(ORDER BY DECODE(LANGUAGE,'E',0,'D',1,2)))[0]::TEXT AS name
FROM cte
GROUP BY Number
ORDER BY Number;

Output:

enter image description here

How does it work:

IFF(LANGUAGE IN ('D','E'), NAME, 'name not available') - allowing only 'D' and 'E'

DECODE(LANGUAGE,'E',0,'D',1,2)) - sorting order, 'E' first, 'D' second

Related: Equivalent for Keep in Snowflake

Alternatively using QUALIFY:

WITH cte(Number, LANGUAGE, Name) AS (
  SELECT 1111,    'D',    'German name for 1111' UNION ALL
  SELECT 1111,    'E',    'English name for 1111' UNION ALL
  SELECT 1111,    'S',    'Spanish name for 1111' UNION ALL
  SELECT 2222,    'D',    'German name for 2222' UNION ALL
  SELECT 3333,    'S',    'Spanish name for 3333'
)
SELECT Number, 
   CASE WHEN LANGUAGE IN ('E','D') THEN NAME ELSE 'name not available' END AS Name
FROM cte
QUALIFY ROW_NUMBER() OVER(PARTITION BY Number 
                          ORDER BY DECODE(LANGUAGE,'E',0,'D',1,2)) = 1
ORDER BY Number;
user_50708790
Related