How to return records from one column only if it contains no null value and if it's all null then only check other columns?

Question

I have a table that looks like this:

ID	First	Second	Third	Amount
1			X	44
2				55
3				33
4				22
5			X	11

I want to select the records from the above table base on whether the columns(First, second, third) have values or not. It must check the column in the same order. If column First has value, then return those records only. If it's all null, only then it should check column Second and then return the records that are not null in column Second. If column Second is all null, then it checks column Third and returns any record that is not null.

For the above table, the sql should return only ID 1 and 5 because column First and Second are all null.

Answer 1

Try using the max window function as the following:

select ID, First, Second, Third, Amount
from
(
  select tb.*,
    max(First) over () max_first,
    max(Second) over () max_second,
    max(Third) over () max_third
  from table_name tb
) t
where max_first is not null and First is not null -- here (max_first is not null) is superfluous, I put it just for clarification, you may remove it.
  or  max_first is null and max_second is not null and Second is not null -- (max_second is not null) is superfluous
  or  max_first is null and max_second is null and max_third is not null and Third is not null -- (max_third is not null) is superfluous

demo