BIGQUERY SQL STATEMENT WHERE SAME RECORD WAS NOT NULL PREVIOUSLY BUT NULL NOW-CodePudding

I have table (bsr_pickpack) with two columns ie asin (STRING) and sales_rank (INTEGER) where same asin (STRING) repeats again but few repeated asin without sales_rank (null). So I tried to get a list of such repeated asin which have null sales_rank later in repeat.

SELECT asin,sales_rank
from bsr_pickpack
WHERE asin !='' AND (sales_rank is not null)
and asin in(
select asin from bsr_pickpack where not (sales_rank is not null)
)

CodePudding user response：

Some records with asin value valueA have their corresponding sales_rank value as NULL will get selected in the inside select clause. The, some other records with the same asin value valueA with their corresponding sales_rank value as not NULL will also match the rest of the filters, since their valueA will be included in the IN clause, but they're not the same records as the ones that passed through the inside select clause.

CodePudding user response：

Finally, this works for me :)

SELECT  asin,sales_rank 
from bsr_pickpack 
WHERE asin!="" 
and asin in( 
select asin from bsr_pickpack where sales_rank is null
) 
order by asin