BigQuery - select rows with condition within id groups

Question

I have a table like this in BigQuery:

ID  CODE     
aa  code-r           
aa  code-k           
aa  code-s
aa  code-special-r
aa  code-special-k
aa  code-t          
bb  code-r           
bb  code-k
bb  code-special-k
bb  code-t          
cc  code-r            
cc  code-k           
cc  code-t

I need to select the rows from each ID where code-special is present, otherwise take the normal code. So the result should be:

ID  CODE               
aa  code-s
aa  code-special-r
aa  code-special-k
aa  code-t          
bb  code-r           
bb  code-special-k
bb  code-t          
cc  code-r            
cc  code-k           
cc  code-t

I was thinking of using rew_number() somehow, but I can't find the solution.

Answer 1

For your requirement you can consider the below query:

CODE:

 select ID,CODE from (select *,rank() over(partition by ID,lastcode order by findlast desc) as r from 
(SELECT *,RIGHT(code,1) as lastcode,CASE when code LIKE "%special%" THEN (RIGHT(code,1)) ELSE NULL END AS findlast FROM table) ) 
where r=1 order by ID;

RESULT:

For more information refer to these link1 and link2.