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

Question

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

Learner DAX 41

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.

0 comments

2 answers

Your answer

Answer 1

LiHongMSFT-4306 31,621

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

Answer 2

Erland Sommarskog 134.7K MVP Volunteer Moderator

; 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

Learner DAX 41 Reputation points

2024-10-14T22:05:18.6366667+00:00

Hi @Erland Sommarskog ,

Thanks for the response. I tried the code and I am seeing the row which has NULL record. I am trying to see the record before the NULL row like below

I am trying to see the row before the NULL value row.

Help with this would be appreciated.
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2024-10-15T18:08:31.11+00:00

Overall, when you ask a question, it helps if you can make it precise. An advice we often give is that you should include the CREATE TABLE script for your table (or a simplified version of it), together with test data, enough to illustrate all angles of the problem. Three rows are rarely enough for that.

Then add to that the expected result given the test data. This makes it possible to copy and paste into a query window to develop a tested solution. Finally, we need a brief description of the business rules that explains why you want that result. Brief, but still exact.

In this case we were told that you wanted the row that come before the NULL. But is that by ID, but DT-Time or something else? Since your description was vague, I did feel like putting a whole of lot energy. Yes, the query I gave you, will also include the last id, since it is not followed by something. But since, reasonably, your actual data has more than three rows, I was not sure if it was a problem, and I did want to write an overly elaborate solution, without knowledge what I'm aiming at.

And since I don't know more today than I did yesterday, I await until I get further clarifying input from you.

Share via

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

2 answers

Your answer