Select all rows after first x rows containing NULLs
I have a table src where each Product-Country combination might contain NULL values in column Country for the first N rows.
How can i select for each Product-Country combination only rows after 1st N rows which contain NULL values in Country field? Its important to also select any latter rows which contain NULL in Country as long as there was some value before it.
For ex. For product A, i would like to select rows with ID 3 and 4(without ID 1 and 2). For Product B, i would like to select rows with ID 6 and 7(without ID 5)
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])
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 AND s.ID >= mp.MinID