Post reply

multiple same dates

  • January 11, 2023 at 3:16 pm

    #4136457

    Hi Expert,

    Have 2 columns where column A which is id having multiple duplicate records

    this is my input records and output is mentioned below

    create table table1

    (id varchar(20), b varchar(20), c date)

    insert into table1

    values('-1','ww','2022-05-24'),

    ('-1', 'ff', '2022-05-24'),

    ('1', 'ee', '2022-05-25'),

    ('1' ,'ww', '2022-05-25'),

    ('2', 'ff', '2022-05-26'),

    ('4', 'ww', '2022-05-27'),

    ('4', 'ff', '2022-05-25'),

    ('4', 'gg', '2022-05-27')

     

    expected out put

     

  • January 11, 2023 at 3:17 pm

    #4136458

    kkk

  • January 11, 2023 at 3:18 pm

    #4136459

    kkk

  • January 11, 2023 at 4:26 pm

    #4136488

    From you required output it appears that you want the last row for each date, but this is based on the order they appear on the screen.

    This will return one row per date, but it will return (-1, 'www') for 24-05-2022. To get (-1, 'ff) there needs to be an additional column that orders the data. You could use a case statement to order these specific rows, but I don't think that would solve the problem.

    SELECT  x.id, x.b, x.c
    FROM (
            SELECT  id, b, c,
                    ROW_NUMBER() OVER (PARTITION BY c ORDER BY id DESC, b DESC) AS RowNum
            FROM    dbo.table1
        ) AS x
    WHERE   x.RowNum = 1;

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply