CODEWITHSUNDEEP
sqlgoogle-bigquery
Fauzi
Fauzi

Reputation: 13

Group by on two fields with different value and choosing specific value by order

I have been searching for the solution to this problem but still couldn't find the most similar problem than this SQL Group BY COLUMN Choose specific rows .

So here is my problem

     Type_Table
column1  |   column2
a        |   s
a        |   m
a        |   e
b        |   s
b        |   e
c        |   m
c        |   s

so basically i want to group by column1 but choosing only the values in column2 = e, but if it is not exist in the duplicated value in column1 then choosing column2 = s, but if it is not exist in the duplicated value column1 then choosing column2 = m. So the resulting table would look like this

column1  |   column2
a        |   e
b        |   e
c        |   s

I've used this select column1,case when column2=e then e when column2=s then s when column2=m then m end column2 from type_table group by 1 but it's obviously not gonna work. What i need is group by column1 and for every different value in column2 choose only e if exist for their respective column1 value but choose s if e doesnt exist and choose m if s doesnt exist. Thanks for your answers

Upvotes: 1

Views: 51

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Below is The BigQuery style (with Standard SQL) 

#standardSQL
SELECT 
  column1, 
  ARRAY_AGG(column2 ORDER BY STRPOS('mse', column2) DESC LIMIT 1)[OFFSET(0)] column2
FROM `project.dataset.table`
GROUP BY column1

You can test, play with above using sample data from your question as in example below 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'a' column1, 's' column2 UNION ALL
  SELECT 'a', 'm' UNION ALL
  SELECT 'a', 'e' UNION ALL
  SELECT 'b', 's' UNION ALL
  SELECT 'b', 'e' UNION ALL
  SELECT 'c', 'm' UNION ALL
  SELECT 'c', 's' 
)
SELECT 
  column1, 
  ARRAY_AGG(column2 ORDER BY STRPOS('mse', column2) DESC LIMIT 1)[OFFSET(0)] column2
FROM `project.dataset.table`
GROUP BY column1
-- ORDER BY column1

with result 

Row column1 column2  
1   a       e    
2   b       e    
3   c       s

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

One method uses row_number():

select t.*
from (select t.*,
             row_number() over (partition by col1
                                order by (case col2 when 'e' then 1 when 's' then 2 when 'm' then 3 else 4 end)
                               ) as seqnum
      from t
     ) t
where seqnum = 1;

Upvotes: 1

Related Questions