I have a SQLAnywhere table like this:
id int not null,
inserted datetime not null,
modified datetime not null,
data1 varchar(20) null
data2 varchar(20) null
The developer didn't put a unique index on the id, and now there are a bunch of rows with the same id but different values for data1 and data2 across the rows, including some nulls. I need to fix that, rolling up all the rows with the same id into single rows in a new table using the earliest inserted time, the latest modified time, and the latest (by modified time) non-null values of data1 and data2 (unless all the rows for that id have null, in which case the rolled-up row value would be null). I can't just use the last row; I need the latest non-null value for each individual column.
So three rows like this:
1, '2016-01-01', '2016-01-02', 'first', null
1, '2016-01-05', '2016-01-07', null, 'second'
1, '2016-01-10', '2016-01-12', 'third', null
...would roll up to:
1, '2016-01-01', 2016-01-12', 'third', 'second'
I've looked at non-equi-self-joins and windowing functions, but I can't quite get them to roll up that latest non-null value for individual columns. Is there a cool SQL way to do this in one query or am I, yikes, writing a Java program for it?
Thanks
I would use LAST_VALUE.
select
id
,min(inserted) over (partition by id)
,max(modified) over (partition by id)
,last_value(data1 ignore nulls) over (partition by id order by modified)
,last_value(data2 ignore nulls) over (partition by id order by modified)
from
table_a