SQL question - Cursor or not?

Question

Hi, I have a query which returns 2+ rows. In those results is a column which we can call columnX for now. Lets look at those example results:

columnX
100
86
85
70
null
null

I get 6 rows for example, some of them are null, some of them are not null. Now I want to go through those results and stop as soon as I find a row which is <> null. How can I do that?

Thanks in advance :-)

Answer 1

I would use a CTE (Common Table Expression) to pull out an index alongside the column (using the ROW_NUMBER function).

You can then use a nested select to find the minimum null index, then select everything below that.

WITH cteMyTable AS
(
    SELECT RUW_NUMBER() OVER (ORDER BY (ColZ) as [Rank], ColumnX
    FROM MyTable
)

SELECT [Rank], ColumnX
FROM cteMyTable WHERE [Rank] < 
   (SELECT MIN([Rank]) FROM cteMyTable WHERE ColumnX IS NULL)

---

Edit following your further information, if you just want all rows where ColumnX is the minimum value, you can jsut do the following:

SELECT * 
FROM MyTable
WHERE ColumnX = (SELECT MIN(ColumnX) FROM MyTable)

Answer 2

Thing is, its always sorted that way. So if there are nulls, they are at the bottom. This can be a result:

columnX
100
86
85
70
null
null

and this as well:

columnX
100
86
85
70

So there can be nulls or not, in any case, I want to find the first not null rows, 70 in the above examples. I think the CTE is a nice start...but something is still missing. This is how I get those rows:

select columnx from mytable where someid = 12345

So the numbers in columnx all belong together (because someid). I really have to get the first not null row from that result. I cant just look for the first row that is not null from the whole columnx, I have to use a condition (where someid = 12345) before. The 70 is just an example, it can be 90 as well, so I cant get the minvalue from the results. The nulls are always at the bottom, the rest can be unsorted. This can be a result as well:

columnX
100
23
80
78
null
null

In that case I want the 78.

Thanks