CODEWITHSUNDEEP
sqlsql-servert-sql
Peter Trevor
Peter Trevor

Reputation: 125

Last value per column in group, one row per group

This should be simple but for some reason I'm stuck. Consider the following data:

KEY1  KEY2  COL1    COL2    COL3
--------------------------------------
1     1     A       7       (null)
1     2     A       8       (null)
1     3     (null)  7       (null)
2     2     (null)  (null)  4
2     4     B       6       (null)
3     1     A       B       (null)

(KEY1 is the Id, KEY2 is the generation, and there are actually about 30 data columns but I'm only listing 3 here for simplicity.)

I want to get one row per Id, and for each column get the last non-null value. In other words...

KEY1  COL1    COL2    COL3
----------------------------
1     A       7       (null)
2     B       6       4
3     A       B       (null)

I tried the following but it seems to do nothing other than echo out all my rows.

SELECT key1,
       LAST_VALUE(col1) OVER (PARTITION BY key1 ORDER BY key2 ASC) AS col1,
       LAST_VALUE(col2) OVER (PARTITION BY key1 ORDER BY key2 ASC) AS col2,
       LAST_VALUE(col3) OVER (PARTITION BY key1 ORDER BY key2 ASC) AS col3
    FROM test1

(And this is for SQL Server 2012 and SQL Server Express.)

Upvotes: 1

Views: 83

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271121

SQL Server does not (yet) support the IGNORE NULL option on window functions. One method is to use conditional aggregation. This requires an intelligent generation of sequence numbers for the columns, to ensure that the value "1" for the sequence is assigned to non-NULL values.

Here is a query that should do this:

select t1.key1,
       max(case when seqnum1 = 1 then col1 end) as col1,
       max(case when seqnum2 = 1 then col2 end) as col2,
       max(case when seqnum3 = 1 then col3 end) as col13
from (select t1.*,
             row_number() over (partition by key1
                                order by (case when col1 is not null then 1 else 2 end),
                                         key2 desc
                               ) as seqnum1,
             row_number() over (partition by key1
                                order by (case when col2 is not null then 1 else 2 end),
                                         key2 desc
                               ) as seqnum2,
             row_number() over (partition by key1
                                order by (case when col3 is not null then 1 else 2 end),
                                         key2 desc
                               ) as seqnum3
      from test1 t1
     ) t1
group by t1.key1

Upvotes: 1

James Z
James Z

Reputation: 12317

If I understood the requirements correctly, shouldn't this work? Might be quite expensive depending on the amount of data / columns.

select
   key1,
   (select top 1 col1 from test1 t2 where t.key1 = t2.key1 and col1 is not null order by key2 desc) as col1,
   (select top 1 col2 from test1 t2 where t.key1 = t2.key1 and col2 is not null order by key2 desc) as col2,
   (select top 1 col3 from test1 t2 where t.key1 = t2.key1 and col3 is not null order by key2 desc) as col3
from 
   (select distinct key1 from test1) t

Upvotes: 1

Related Questions