SQL Exclude rows on specific conditions-CodePudding

I want to exclude in my MSSQL View some results which are "duplicated with null value"

stock_id  Current  BrandName  Made_in
1            1         X       Canada
1            1        NULL     NULL
2            1         y       USA
3            1         z       Nigeria
4            1         T       Cambodia

So I want to exclude the stock ID which are duplicated and NULL in the columns brand_name and made_in. Results :

stock_id  Current  BrandName  Made_in
1            1         X       Canada
2            1         y       USA
3            1         z       Nigeria
4            1         T       Cambodia

thats my try but it exclude now both stock_id

WHERE table.is_current = 1 and not (stock_id='1' and brand_name=null);

CodePudding user response：

To exclude nulls you can use max function.

select stock_id,
       Current,
       max(BrandName) as BrandName ,
       max(Made_in) as Made_in
from test_tbl
group by stock_id,Current;

If you have nulls on current you can apply max(Current), too.