max date order by status

Shambhu Rai 1,411 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,311 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} votes

13 answers

Sort by: Most helpful
  1. Shambhu Rai 1,411 Reputation points
    2022-04-19T20:37:11.63+00:00

    Hi Expert,
    there is small change in condition when same value comes for the status on next day or same month then out put should be min date
    INSERT INTO TEST
    VALUES

    ('2022-03-28 15:58:40','Newtwo', '6-1'),
    ('2022-03-29 12:52:49','Newtwo', '6-1')
    ('2022-03-31 12:52:49','Newtwo', '6-1')
    ('2022-04-01 12:52:49','Newtwo', '6-1')

    expected output
    ('2022-03-28 15:58:40','Newtwo', '6-1'),

    0 comments No comments

  2. Shambhu Rai 1,411 Reputation points
    2022-04-19T21:10:53.427+00:00

    Click to vote
    0 Votes"
    0
    ShambhuRai-4099 answered • 32 minutes ago
    Hi Expert,
    there is small change in condition when same value comes for the status on next day or same month then out put should be min date
    INSERT INTO TEST
    VALUES

    ('2022-03-28 15:58:40','Newtwo', '6-1'),
    ('2022-03-29 12:52:49','Newtwo', '6-1')
    ('2022-03-31 12:52:49','Newtwo', '6-1')
    ('2022-04-01 12:52:49','Newtwo', '6-1')

    expected output
    ('2022-03-28 15:58:40','Newtwo', '6-1'),

    0 comments No comments

  3. LiHong-MSFT 10,046 Reputation points
    2022-04-20T02:53:42.02+00:00

    Hi @Shambhu Rai
    Help us help you! When Insert these sample datas, then what is you expected output now?

     INSERT INTO TEST  
     VALUES  
    ('2022-05-28 15:58:40','Newthree', '8-1'),  
    ('2022-05-28 17:52:49','Newthree', '8-1'),  
    ('2022-05-31 17:52:49','Newthree', '8-1'),  
    ('2022-03-28 15:58:40','Newtwo', '6-1'),  
    ('2022-03-29 12:52:49','Newtwo', '6-1'),  
    ('2022-03-29 15:55:50','Newtwo', '6-1'),  
    ('2022-04-01 12:52:49','Newtwo', '6-1'),  
    ('2022-03-28 12:52:49','Newtwo', '4-1'),  
    ('2022-03-28 19:53:40','Newtwo', '4-1'),  
    ('2022-01-04 18:00:57','Newone','3-1'),  
    ('2022-01-04 19:00:57','Newone','3-1'),  
    ('2022-04-04 18:00:57','Newone','2-1'),  
    ('2022-04-04 19:00:57','Newone','1-1')  
    

    Best regards,
    LiHong

    0 comments No comments