max date order by status

Shambhu Rai 1,406 Reputation points
2022-04-14T18:50:09.393+00:00

Hi Expert,

i wanted to fetch status and max new date , value data

create table test
(newdate date,status varchar(10, value varchar(10))

insert into test
values('01-07-2020','Newone','segmentone'),('07-07-2020','Newone','segmenwo'),('09-07-2020','Newtwo','segmenthee'),('10-07-2020','Newtwo','segmenthee')

expected output

193179-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.
13,047 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,575 questions
0 comments No comments
{count} votes

13 answers

Sort by: Most helpful
  1. Naomi 7,361 Reputation points
    2022-04-14T19:04:16.827+00:00

    Try:

    ;WITH cte AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY [Status] ORDER BY [NewDate] DESC) AS Rn
    FROM #test)
    
    SELECT [NewDate], [Status], [Value] FROM cte WHERE Rn = 1;
    
    0 comments No comments

  2. Shambhu Rai 1,406 Reputation points
    2022-04-18T15:30:44.967+00:00

    HI Expert,

    This is perfect. but the data needs max(date) should be order by same day and not all calendar days

    insert into test

    values

    ('2022-01-04 18:00:57','Newone','segmentone'),('2022-01-04 19:00:57','Newone','segmenwo'),
    ('2022-04-04 18:00:57','Newtwo','segmenthee'),('2022-04-04 19:00:57','Newtwo','segmenfour')

    expected output:

    ('2022-01-04 19:00:57','Newone','segmenwo'),

    ('2022-04-04 19:00:57','Newtwo','segmenfour')


  3. Jingyang Li 5,891 Reputation points
    2022-04-18T15:59:50.137+00:00

    You need a datetime data type for what you want.

    0 comments No comments

  4. LiHong-MSFT 10,046 Reputation points
    2022-04-19T06:31:52.867+00:00

    Hi @Shambhu Rai
    First,you need to ensure that the datatype of 'newdate' column is DATETIME.
    Then,to get max(date),here are two solutions:
    1.Use Row_number()over(Partition by status Order by ...)

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

    2.Use MAX() along with JOIN:

    ;WITH CTE AS  
    (  
     SELECT MAX(newdate)AS Max_newdate,status  
     FROM #test  
     GROUP BY status  
    )  
    SELECT C.Max_newdate,C.status,T.value  
    FROM CTE C JOIN #test T ON C.Max_newdate=T.newdate AND C.status=T.status  
    

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  5. Shambhu Rai 1,406 Reputation points
    2022-04-19T14:08:21.87+00:00

    Hi Expert,

    there is condition when value is same for the status then it should take min date and if the value is change for the status then max date

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

    insert into test

    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')

    expected output

    ('2022-01-04 18:00:57','Newone','segmentone')
    ('2022-04-04 19:00:57','Newtwo','segmentfour')

    0 comments No comments