Moon Moon - 7 months ago 10
SQL Question

SQL query to select last non null node.attribute/field

Let say my table looks like this

Point-ID | Pos-1 | Pos-2 | Pos-3 | Pos-4
---------|-------|-------|-------|-------
P1 | a1 | a2 | Null | NULL
P2 | b1 | b2 | b3 | NULL
P3 | c1 | c2 | c3 | c4
P4 | d1 | Null | Null | Null
P5 | e1 | e2 | e3 | e4


From the above I want to select the first Pos & the last null pos so that my resultant table looks like this

Point-ID | Start | End
---------|-------|-------
P1 | a1 | a2
P2 | b1 | b3
P3 | c1 | c4
P4 | d1 | d1
P5 | e1 | e4


Any idea how to do it? So far what I could think of is something like

Select
Point-ID,
(CASE
WHEN Pos-4 is not null then Pos-4
WHEN Pos-3 is not null then Pos-3
WHEN Pos-2 is not null then Pos-2
WHEN Pos-1 is not null then Pos-1
ELSE null
END) as Start,
(CASE
WHEN Pos-4 is not null then Pos-4
WHEN Pos-3 is not null then Pos-3
WHEN Pos-2 is not null then Pos-2
WHEN Pos-1 is not null then Pos-1
ELSE null
END) as End
FROM myTable


p.s I am going be using PROC SQL in SAS Base

Answer

You can do this with function COALESCE:

SELECT Point-ID,
    COALESCE(Pos-1, Pos-2, Pos-3, Pos-4) as Start,
    COALESCE(Pos-4, Pos-3, Pos-2, Pos-1) as End
FROM myTable

COALESCE selects first non null value, so in case of start you make sure to pass columns from 1 to 4, and for end - in the opposite order.

Comments