How to get the non null row before the row with null value

Learner DAX 41 Reputation points
2024-10-14T19:48:43.02+00:00

Hi

I want to see the non null row before the row with NULL value in the result set.

I have data in below in format

User's image

I want to see ID 2 row in the result set like below

User's image

Help with this would be appreciated.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,009 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
102 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,657 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 112.7K Reputation points MVP
    2024-10-14T20:32:29.5066667+00:00
    ; WITH CTE AS (
        SELECT Id, "DT-time", Other_ID, 
              nextOther_ID = LEAD(Other_ID) OVER(ORDER BY Id)
        FROM tbl
    )SELECT Id, "DT-time", Other_ID
    FROM   CTE
    WHERE  nextOther_ID IS NULL
    

  2. LiHongMSFT-4306 28,041 Reputation points
    2024-10-15T01:29:10.57+00:00

    Hi @Learner DAX

    Based on Erland's code, how about modifying where clause like this:

    ; WITH CTE AS (
        SELECT Id, "DT-time", Other_ID, 
              nextOther_ID = LEAD(Other_ID) OVER(ORDER BY Id)
        FROM tbl
    )SELECT Id, "DT-time", Other_ID
    FROM   CTE
    WHERE  nextOther_ID IS NULL AND Other_ID IS NOT NULL
    

    Best regards,

    Cosmog


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.