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)

    CODE:

    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')



  • 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
    

    https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=c4a3c654942ffe1b84e14cf046822d4e




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2