CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Kati XX
Kati XX

Reputation: 23

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


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:

So the result table should like:

Number Name
1111 English name for 1111
2222 German name for 2222
3333 name not available

Upvotes: 2

Views: 414

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176124

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;

Upvotes: 1

Related Questions