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

user_8669616 0
Viewed 61

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
sql-server-2012 sql-server sql
3 Answers

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
user_69148640

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;
user_11440350

Please do thorough testing before relying on the given script as it is based on the given tiny sample of data.

SELECT ID, NAME, LOC
FROM (
    SELECT *, ROW_NUMBER() OVER (
            PARTITION BY ID ORDER BY ID
            ) AS run
    FROM Table_1
    WHERE ID NOT IN (
            SELECT ID
            FROM Table_1
            WHERE LOC = 'NY' AND Name IS NOT NULL
            ) AND NAME IS NOT NULL
    ) A
WHERE run = 1

UNION ALL

SELECT *
FROM Table_1
WHERE LOC = 'NY' AND Name IS NOT NULL
ORDER BY ID
user_61647540
Related