CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Avinash
Avinash

Reputation: 543

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

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

Upvotes: 5

Views: 76

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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;

Upvotes: 2

Fahmi
Fahmi

Reputation: 37473

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

Upvotes: 3

Sai Abhiram Inapala
Sai Abhiram Inapala

Reputation: 359

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

Upvotes: 1

Related Questions