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
max timestamp by status
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
-
Nandan Hegde 29,901 Reputation points MVP
2022-04-20T13:06:57.63+00:00
2 additional answers
Sort by: Newest
-
LiHong-MSFT 10,046 Reputation points
2022-04-21T02:36:18.747+00:00 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 -
Shambhu Rai 1,406 Reputation points
2022-04-20T12:51:14.547+00:00 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 = 1but incorrect data