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-19T15:40:07.077+00:00

    suggestion please


  2. Jingyang Li 5,891 Reputation points
    2022-04-19T16:26:16.727+00:00

    From your new question:
    create table test
    (newdate datetime,status varchar(10), value varchar(10))

    insert into test (newdate,status,value) 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')

    ;with mycte as (
    select newdate,status,value
    ,count(*) over(Partition by status,value) cnt
    ,row_number() Over(Partition by status Order by newdate) rnAsc
    ,row_number() Over(Partition by status Order by newdate DESC) rnDesc
    from test
    )

    select newdate,status,value
    from mycte
    WHERE (cnt>1 and rnAsc=1) or (cnt=1 and rnDesc=1)

    drop table test

    0 comments No comments

  3. Shambhu Rai 1,411 Reputation points
    2022-04-19T16:44:21.037+00:00

    Hi Expert,

    it is showing wrong output in addition to above adding 2 more records
    INSERT INTO TEST
    VALUES

    ('2022-03-28 15:58:40','Newtwo', '6-1'),
    ('2022-03-28 12:52:49','Newtwo', '4-1')

    expected answer

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


  4. Shambhu Rai 1,411 Reputation points
    2022-04-19T17:43:55.107+00:00

    Hi Expert,

    it is showing wrong output in addition to above adding 2 more records
    INSERT INTO TEST
    VALUES

    ('2022-03-28 15:58:40','Newtwo', '6-1'),
    ('2022-03-28 12:52:49','Newtwo', '4-1')

    expected answer

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


  5. Jingyang Li 5,891 Reputation points
    2022-04-19T19:37:54.16+00:00

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

    insert into test (newdate,status,value) 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-1')
    ,('2022-03-28 12:52:49','Newtwo', '4-1')

    ;with mycte as (
    select newdate,status,value
    ,count(*) over(Partition by Cast(newdate as date),status,value) cnt
    ,row_number() Over(Partition by Cast(newdate as date), status Order by newdate) rnAsc
    ,row_number() Over(Partition by Cast(newdate as date),status Order by newdate DESC) rnDesc
    from test
    )

    select newdate,status,value
    from mycte
    WHERE (cnt>1 and rnAsc=1) or (cnt=1 and rnDesc=1)

    drop table test
    /*
    newdate status value
    2022-01-04 18:00:57.000 Newone segmentone
    2022-03-28 15:58:40.000 Newtwo 6-1
    2022-04-04 19:00:57.000 Newtwo segmenfour
    */