MSSQL Query the latest row

Question

I have a table like in the following

col1 col2 col3 col4
1 null A 2021-04-30 14:20
1 B null 2021-04-30 14:22
1 C null 2021-04-30-14:23
2 D null 2021-04-30 14:25
2 null E 2021-04-30 14:26

I would like to query to have a result

1 C A 2021-04-30 14:27
2 D E 2021-04-30 14:28

how to write that ? using RANK / ROW_OVER ?

Answer 1

Try one of approaches:

;
with Q as
(
    select *, 
        LAST_VALUE(col2) over (partition by col1 order by iif(col2 is null, 0, 1), col4 range between unbounded preceding and unbounded following) as lv,
        FIRST_VALUE(col3) over (partition by col1 order by iif(col3 is null, 1, 0), col4 range between unbounded preceding and unbounded following) as fv,
        row_number() over (partition by col1 order by col4) as rn1,
        dense_rank() over (order by col1) as rn2,
        max(col4) over () as mx
    from atable
)
select
    col1, 
    lv as col2,
    fv as col3,
    dateadd(minute, rn2, mx) as col4
from Q
where rn1 = 1
order by col1

Answer 2

Hi @sakuraime ,

Please also check:

CREATE TABLE yourtable(col1 int,col2 char(15),col3 char(15),col4 datetime)  
INSERT INTO yourtable VALUES(1,null,'A','2021-04-30 14:20'),  
                            (1,'B',null,'2021-04-30 14:22'),  
 (1,'C',null,'2021-04-30 14:23'),  
 (2,'D',null,'2021-04-30 14:25'),  
 (2,null,'E','2021-04-30 14:26')  
  
;WiTH cte  
as(SELECT DISTINCT col1,MAX(col2) OVER(PARTITION BY col1 ORDER BY col1) mm  
FROM yourtable)  
,cte2 as(SELECT DISTINCT col1,MAX(col3) OVER(PARTITION BY col1 ORDER BY col1) mm  
FROM yourtable)  
  
SELECT c1.*,c2.mm,FORMAT(DATEADD(mi,c1.col1,(SELECT MAX(col4) FROM yourtable)),N'yyyy-MM-dd HH:mm')  
FROM cte c1  
JOIN cte2 c2 ON c1.col1=c2.col1  

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.