This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(198.4, 18%, 29%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ELD%3C/text%3E%3C/svg%3E)
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
I want to see ID 2 row in the result set like below
Help with this would be appreciated.
; 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
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.
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.
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".