question

sakuraime avatar image
0 Votes"
sakuraime asked EchoLiu-msft commented

MSSQL Query the latest row

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 ?

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered sakuraime commented

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


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thanks. let me try

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft commented

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:
93223-image.png

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.



image.png (3.2 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

93463-image.png


thanks. but there is an issue if MAX is order by alphabet ...

93513-image.png


0 Votes 0 ·
image.png (57.0 KiB)
image.png (57.0 KiB)

Hi,
What is the expected result of the code in your screenshot?
Is it like below:
93571-image.png

In addition, in the data you provided, what are the rules for obtaining the following results?
93456-image.png
C A
D E
Is there any basis for the above combination?


Echo


0 Votes 0 ·
image.png (3.1 KiB)
image.png (4.6 KiB)

thanks. I think

using LAST_VALUE() instead of MAX() works better for my case .

0 Votes 0 ·
Show more comments