Select all rows after first x rows containing NULLs

Question

I have table src. In this table, each Product-Country combination might contain NULL in the Country column for the first x rows.

How can I SELECT rows for each Product-Country combination, only rows after the first x rows that contain NULL in the Country column? It's important to also SELECT any latter rows, even if they do contain NULL in the Country column, as long as there was at least one row with a true value, prior to it.

Expected Output

For example.

1. For product A, I would like to SELECT rows with ID 3 and 4, but not ID 1 and 2.
2. For Product B, I would like to SELECT rows with ID 6 and 7, but not ID 5.

ID	Product	Country	Timestamp
3	A	AT	2022-01-23 14:29:06.830
4	A	NULL	2022-01-24 14:29:06.830
6	B	CH	2022-01-23 14:29:06.830
7	B	NULL	2022-01-24 14:29:06.830

Sample Table and Data

create table dbo.src
(
    ID int not null
    ,Product varchar(2) not null
    ,Country varchar(2) null
    ,[Timestamp] datetime not null
)

insert into dbo.src
    (ID,Product, Country, [Timestamp])
values
    (1,'A',NULL,'2022-01-21 14:29:06.830')
    ,(2,'A',NULL,'2022-01-22 14:29:06.830')
    ,(3,'A','AT','2022-01-23 14:29:06.830')
    ,(4,'A',NULL,'2022-01-24 14:29:06.830')
    ,(5,'B',NULL,'2022-01-22 14:29:06.830')
    ,(6,'B','CH','2022-01-23 14:29:06.830')
    ,(7,'B',NULL,'2022-01-24 14:29:06.830')

Answer 1

We can create one enumeration per product and one per product, country. By subtracting these two numbers, we get detection of when a product changes the country. Let's call this constructed attribute grp. To handle the case when the first country is not null, we add 1 to grp for these. By filtering out all rows where grp = 0, we get the expected result:

select ID, Product, Country, [Timestamp]
from (
  select s.*
       , case when country is not null then 1 else 0 end
       + row_number() over (partition by Product order by [Timestamp]) 
       - row_number() over (partition by Product, country order by [Timestamp]) grp
  from dbo.src s
) as T
where grp > 0
order by product, [Timestamp]

ID	Product	Country	Timestamp
2	A	AT	2022-01-22 14:29:06.830
3	A	AT	2022-01-23 14:29:06.830
4	A		2022-01-24 14:29:06.830
6	B	CH	2022-01-23 14:29:06.830
7	B		2022-01-24 14:29:06.830
8	C	CH	2022-01-24 14:29:06.830

Fiddle2

Answer 2

Something like this should work. Use a cte to find the first ID for each product that is not null, then join back to the original table.

;WITH cMinProductId AS (
    SELECT Product, MIN(ID) MinID
    FROM dbo.src
    WHERE Country IS NOT NULL
    GROUP BY Product
)
SELECT s.*
FROM dbo.src AS s
INNER JOIN cMinProductId AS mp
    ON s.Product = mp.Product
WHERE s.ID >= mp.MinID

ID	Product	Country	Timestamp
3	A	AT	2022-01-23 14:29:06.830
4	A	NULL	2022-01-24 14:29:06.830
6	B	CH	2022-01-23 14:29:06.830
7	B	NULL	2022-01-24 14:29:06.830

Fiddle