max timestamp by status

Shambhu Rai 1,406 Reputation points
2022-04-20T12:09:07.347+00:00

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

194733-image.png

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,815 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
0 comments
Accepted answer
  1. Nandan Hegde 29,896 Reputation points MVP
    2022-04-20T13:06:57.63+00:00 
    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
    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. 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 = 1

    but incorrect data

  2. 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

    0 comments