8

I am using SQL Server 2016 and the data I am consuming has the following form.

CREATE TABLE #tab (cat CHAR(1), t CHAR(2), val1 INT, val2 CHAR(1));

INSERT INTO #tab VALUES 
    ('A','Q1',2,NULL),('A','Q2',NULL,'P'),('A','Q3',1,NULL),('A','Q3',NULL,NULL),
    ('B','Q1',5,NULL),('B','Q2',NULL,'P'),('B','Q3',NULL,'C'),('B','Q3',10,NULL);

SELECT *
FROM    #tab;

enter image description here

I would like a obtain the last non-null values over columns val1 and val2 grouped by cat and ordered by t. The result I am seeking is

cat  val1 val2
A    1    P
B    10   C

The closest I have come is using LAST_VALUE while ignoring the ORDER BY which is not going to work since I need the ordered last non-null value.

SELECT DISTINCT 
        cat, 
        LAST_VALUE(val1) OVER(PARTITION BY cat ORDER BY (SELECT NULL) ) AS val1,
        LAST_VALUE(val2) OVER(PARTITION BY cat ORDER BY (SELECT NULL) ) AS val2
FROM    #tab
cat  val1 val2
A    NULL NULL
B    10   NULL

The actual table has more columns for cat (date and string columns) and more val columns (date, string, and number columns) to select the last non-null value.

Any ideas how to make this selection.

  • 1
    @Vérace Grouped by cat ordered by t. – Edmund Jun 6 '18 at 20:04
  • 1
    @ypercubeᵀᴹ No, there is no missing Q4 value, the t values repeat. It is not well behaved data. – Edmund Jun 6 '18 at 20:27
  • 4
    All right but in that case, you have to provide a order that determines a perfect ordering. PARTITION BY cat ORDER BY t, id for example. Otherwise, the same query (any query) may give you different results on separate executions. If the columns in the table are only the ones you show, I don't see how we can have a determinate order however! – ypercubeᵀᴹ Jun 6 '18 at 20:55
  • 1
    @ypercubeᵀᴹ Therein lies the challenge. There is no id column in the data. The there are multiple grouping columns, a string column that can be used for within group ordering, and then the multiple value columns with nulls interspersed. – Edmund Jun 6 '18 at 21:25
  • 1
    If you can't tell SQL Server deterministically what order the rows should be, how is any consumer of this data going to know the difference? – Aaron Bertrand Jun 7 '18 at 2:57
8

Using the concatenation technique from The Last non NULL Puzzle by Itzik Ben Gan would look like this with your sample table and column data types.

select T.cat,
       cast(substring(
                     max(cast(T.t as binary(2)) + cast(T.val1 as binary(4))),
                     3,
                     4
                     ) as int),
       cast(substring(
                     max(cast(T.t as binary(2)) + cast(T.val2 as binary(1))),
                     3,
                     1
                     ) as char(1))
from #tab as T
group by T.cat;

enter image description here

Another way to write this query that divides the steps into CTE's to perhaps better show what is going on. It gives the exact same execution plan as the query above.

with C1 as
(
  -- Concatenate the ordering column with the value column
  select T.cat,
        cast(T.t as binary(2)) + cast(T.val1 as binary(4)) as val1,
        cast(T.t as binary(2)) + cast(T.val2 as binary(1)) as val2
  from #tab as T
),
C2 as
(
  -- Get the max concatenated value per group
  select C1.cat,
         max(C1.val1) as val1,
         max(C1.val2) as val2
  from C1
  group by C1.cat
)
-- Extract the value from the concatenated column
select C2.cat,
       cast(substring(C2.val1, 3, 4) as int) as val1,
       cast(substring(C2.val2, 3, 1) as char(1)) as val2
from C2;

This solution uses the fact that concatenating a null value with something results in a null value. SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)

  • Very well distilled Mikael. This solution has saved me a number of times, though I found the ending of Itzik's article confusing at first. In that he labelled it "step 2" when in reality it was more like implementing the logic behind step 1. – pim Aug 7 '18 at 13:31
2

Just add a check for NULL in the partition will do

SELECT DISTINCT 
        cat, 
        FIRST_VALUE(val1) OVER(PARTITION BY cat ORDER BY CASE WHEN val1 is NULL then 0 else 1 END DESC, t desc) AS val1,
        FIRST_VALUE(val2) OVER(PARTITION BY cat ORDER BY CASE WHEN val2 is NULL then 0 else 1 END DESC, t desc) AS val2
FROM    #tab
0

This should do it. row_number() and a join

If you don't have a good sort you have to hope only one of the Q3 is not null.

declare @t TABLE (cat CHAR(1), t CHAR(2), val1 INT, val2 CHAR(1));
INSERT INTO @t VALUES 
    ('A','Q1',2,NULL),('A','Q2',NULL,'P'),('A','Q3',1,NULL),('A','Q3',NULL,NULL),
    ('B','Q1',5,NULL),('B','Q2',NULL,'P'),('B','Q3',NULL,'C'),('B','Q3',10,NULL);

--SELECT *
--     , row_number() over (partition by cat order by t) as rn
--FROM   @t
--where val1 is not null or val2 is not null;

select t1.cat, t1.val1, t2.val2 
from  ( SELECT t.cat, t.val1
             , row_number() over (partition by cat order by t desc) as rn
        FROM   @t t
        where val1 is not null 
       ) t1
join   ( SELECT t.cat, t.val2
             , row_number() over (partition by cat order by t desc) as rn
        FROM   @t t
        where val2 is not null 
       ) t2
   on t1.cat = t2.cat
  and t1.rn = 1
  and t2.rn = 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.