GROUP BY and take the given value if it is not empty

Question

I have a table

+-----+-----------+-----+
| ID  | NAME      | LOC |
+-----+-----------+-----+
| 101 | Stainless | NY  |
| 101 | Brass     | MUM |
| 102 |           | NY  |
| 102 | Gold      | JP  |
| 103 | Silver    | CN  |
| 103 | Aluminium | US  |
| 104 | Platinum  | NY  |
| 104 | Diamond   | UK  |
| 105 |           | NY  |
+-----+-----------+-----+

I want to group by and take the NAME correspoding to NY location. If the NAME is empty for NY then take the other one. Incase if NY is not available for any ID, then take any NAME from other locations. And if some ID is present only once with empty NAME, then we can take the same.

Expected output

+-----+-----------+
| ID  | NAME      |
+-----+-----------+
| 101 | Stainless |
| 102 | Gold      |
| 103 | Silver    |
| 104 | Platinum  |
| 105 |           |
+-----+-----------+

I have tried the below query. But it is not giving my expected output. What am i missing?

SELECT ID, MAX(CASE WHEN LOC='NY' AND NAME!='' THEN NAME END) NAME
FROM MYTABLE  
GROUP BY ID

Answer 1

You can use row_number() - DEMO HERE

select * from
(
SELECT *,
row_number() over(partition by id order by case when NAME='' then -1 when LOC='NY' AND NAME!='' then 1 else 0 end desc) as rn
FROM t 
)A where rn=1

Answer 2

You can use aggregation like this:

select id,
       coalesce( max(case when location = 'NY' then name end),
                 max(name)
               ) as desired_name
from t
group by id;

Note: This assumes that the blank names are really NULL. If they are not then tweak the logic:

select id,
       coalesce( max(case when location = 'NY' and name <> '' then name end),
                 max(name)
               ) as desired_name
from t
group by id;