12

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.

4
  • You still have 2 rows with same cat and t (cat=A, t=Q3) and (cat=B, t=Q3). Were these supposed to be Q4? Jun 6, 2018 at 20:23
  • 1
    @ypercubeᵀᴹ No, there is no missing Q4 value, the t values repeat. It is not well behaved data.
    – Edmund
    Jun 6, 2018 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! Jun 6, 2018 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, 2018 at 21:25

3 Answers 3

12

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)

4
  • It breaks if either of the two Val columns for Q3 are both not null. I wouldn't rely on this unless there's another column in the table that the OP hasn't posted that does preserve the order. The basic method is outstanding but there must be something that reliably and uniquely enforces the order.
    – Jeff Moden
    Jan 16, 2023 at 1:35
  • @JeffModen yes, that is true. Without a reliable order you could get either value as "last" as Ypercube pointed that out in a comment to the question. Jan 16, 2023 at 6:38
  • Since he posted that before the first answer was posted, I'm curious then, why folks posted solutions that could actually hurt the OP.
    – Jeff Moden
    Jan 17, 2023 at 2:56
  • As I see it, use @Kelvin solution if you need it readable, or use Mikael's solution if you need it performant. I wonder what everyone thinks.
    – naviram
    Jul 4, 2023 at 9:58
9

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
1
  • It breaks if Q3 has two non-null values. Also, FIRST_VALUE() reminds me a lot of the FORMAT() function... super easy to use but comparatively super slow.
    – Jeff Moden
    Jan 16, 2023 at 1:33
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
1
  • "If you don't have a good sort you have to hope only one of the Q3 is not null." Spot on. It absolutely breaks if you don't. I wouldn't rely on this unless there's another column in the table that the OP hasn't posted that does preserve the order.
    – Jeff Moden
    Jan 16, 2023 at 1:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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