max timestamp by status

Question

max timestamp by status

Shambhu Rai 1,411

Hi Expert,

i wanted to find it out max timestamp in a day

create table test2
(newdate datetime,status varchar(10), value varchar(10))

insert into test2

values

('2022-01-04 18:00:57','Newone','segmentone')
,('2022-01-04 19:00:57','Newone','segmentone'),
('2022-04-04 18:00:57','Newtwo','segmenthee')
,('2022-04-04 19:00:57','Newtwo','segmenfour'),
('2022-03-28 15:58:40','Newtwo', '6-Jan'),
('2022-03-28 12:52:49','Newtwo', '4-1'),
('2022-03-31 12:52:49','Newtwo', '6-Jan'),
('2022-04-01 12:52:49','Newtwo', '6-Jan'),
('2022-04-28 15:58:40','Newtwo', '6-Jan'),
('2022-04-29 12:52:49','Newtwo', '6-Jan'),
('2022-04-30 12:52:49','Newtwo', '6-Jan'),
('2022-05-01 12:52:49','Newtwo', '6-Jan'),
('2022-05-29 12:52:49','Newtwo', '6-Jan'),
('2022-05-31 12:52:49','Newtwo', '6-Jan'),
('2022-06-01 12:52:49','Newtwo', '6-Jan'),
('2022-06-02 12:52:49','Newtwo', '6-Jan')

expected output

Accepted answer

2 additional answers

Your answer

Answer 1

Nandan Hegde 36,156 MVP Volunteer Moderator

WITH CTE AS
(
SELECT *,ROW_NUMBER()OVER(PARTITION BY status,cast(newdate as date) ORDER BY newdate DESC) AS RowNum
FROM test2
)
SELECT newdate,status,value
FROM CTE
WHERE RowNum = 1

Answer 2

Shambhu Rai 1,411

i tried

;WITH CTE AS
(
SELECT *,ROW_NUMBER()OVER(PARTITION BY status ORDER BY newdate DESC) AS RowNum
FROM test2
)
SELECT newdate,status,value
FROM CTE
WHERE RowNum = 1

but incorrect data

Nandan Hegde 36,156 Reputation points MVP Volunteer Moderator

2022-04-20T13:05:03.44+00:00

you need to partition by Date format of the column and status in your query
Shambhu Rai 1,411 Reputation points

2022-04-20T13:06:49.41+00:00

can you give some example
Nandan Hegde 36,156 Reputation points MVP Volunteer Moderator

2022-04-27T16:54:00.737+00:00

@Shambhu Rai : Did the below solution answer your query ?
If yes, can you please accept it as answer as it might benefit other community members as well

Answer 3

Hi @Shambhu Rai
The inappropriate Partition by causes your result to be wrong.
In your code,you write this: ROW_NUMBER()OVER(PARTITION BY status ORDER BY newdate DESC)
Adding a PARTITION BY clause on the status column, which means the row_number will restart the numbering when the status value changes.There are only two different status in the original data, and that's why you get two max date acorresponding to the two different status.

If you want to get max timestamp in a same day, then you need to PARTITION BY cast(newdate as date)
Check this:

;WITH CTE AS  
(  
SELECT *,ROW_NUMBER()OVER(PARTITION BY CAST(newdate AS DATE) ORDER BY newdate DESC) AS RowNum  
FROM test2  
)  
SELECT newdate,status,value  
FROM CTE  
WHERE RowNum = 1

Best regards,
LiHong

Share via

max timestamp by status

2 additional answers

Your answer