How do I find the result that is not null?

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?

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 lo开发者_开发知识库ok 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.

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)