How to remove duplicate value from the rows in sql

  • Hi Team,

    I am retrieving files and file created date and file modified date from table based on the created date. All files are same file name. My output is looks like below. In the below output,  sep 1 file is sending twice a day in File sending date (09/01 & 09/30). I need the latest sending date file instead of duplicate file. Can yo please help on this?

    File Name      Created Date       File sending Date

    File_XXXX    1-Sep                     9/1/2021

    File_XXXX    2-Sep                    9/2/2021

    File_XXXX    3-Sep                    9/3/2021

    File_XXXX    6-Sep                    9/6/2021

    File_XXXX    1-Sep                     9/30/2021

    File_XXXX   30-Sep                   9/30/2021

  • SELECT [File Name], [Created Date], MAX([File sending Date] AS [File sending Date]

    FROM <your_table_name>

    GROUP BY [File Name], [Created Date]

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • shasivashok wrote:

    Hi Team,

    I am retrieving files and file created date and file modified date from table based on the created date. All files are same file name. My output is looks like below. In the below output,  sep 1 file is sending twice a day in File sending date (09/01 & 09/30). I need the latest sending date file instead of duplicate file. Can yo please help on this?

    File Name      Created Date       File sending Date File_XXXX    1-Sep                     9/1/2021 File_XXXX    2-Sep                    9/2/2021 File_XXXX    3-Sep                    9/3/2021 File_XXXX    6-Sep                    9/6/2021 File_XXXX    1-Sep                     9/30/2021 File_XXXX   30-Sep                   9/30/2021

    What is the actual data type for the Created Date column?  Don't answer that... instead, read the article located at the first link in my signature line below for how to post "Readily Consumable" test data, which will also perfectly explain everything we need to know about your data.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Thanks for your reply. This is not working.  I need an output like below.

    File Name      Created Date       File sending Date

    File_XXXX    2-Sep                    9/2/2021

    File_XXXX    3-Sep                    9/3/2021

    File_XXXX    6-Sep                    9/6/2021

    File_XXXX    1-Sep                     9/30/2021

    File_XXXX   30-Sep                   9/30/2021

  • Scott's solution was correct, with the possible exception of the ordering of results. Here is code to prove it.

    DECLARE @x TABLE
    (
    FileName VARCHAR(50) NOT NULL
    ,CreatedDate DATE NOT NULL
    ,FileSendingDate DATE NOT NULL
    );

    INSERT @x
    (
    FileName
    ,CreatedDate
    ,FileSendingDate
    )
    VALUES
    ('File_XXXX', '20210901', '20210901')
    ,('File_XXXX', '20210902', '20210902')
    ,('File_XXXX', '20210903', '20210903')
    ,('File_XXXX', '20210906', '20210906')
    ,('File_XXXX', '20210901', '20210930')
    ,('File_XXXX', '20210930', '20210930');

    SELECT x.FileName
    ,x.CreatedDate
    ,FileSendingDate = MAX(x.FileSendingDate)
    FROM @x x
    GROUP BY x.FileName
    ,x.CreatedDate
    ORDER BY MAX(x.FileSendingDate)
    ,x.CreatedDate;

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • Thanks Scott & Phill. It's working fine now. I am added another column as Status and the data looks like in the table now.

    File Name      Created Date       File sending Date   Status

    File_XXXX    1-Sep                     9/1/2021                 Pass 02:52 PM

    File_XXXX    2-Sep                    9/2/2021                 Pass 02:42 PM

    File_XXXX    3-Sep                    9/3/2021                 Pass 01:22 PM

    File_XXXX    6-Sep                    9/6/2021                 Pass 12:44 PM

    File_XXXX    1-Sep                     9/30/2021              Fail 11:41 PM

    File_XXXX   30-Sep                   9/30/2021              Fail 11:49 PM

     

    If I used the same query, I am getting all the input like above table. I need the output like below.

    File Name      Created Date       File sending Date   Status

    File_XXXX    2-Sep                    9/2/2021                 Pass 02:42 PM

    File_XXXX    3-Sep                    9/3/2021                 Pass 01:22 PM

    File_XXXX    6-Sep                    9/6/2021                 Pass 12:44 PM

    File_XXXX    1-Sep                     9/30/2021              Fail 11:41 PM

    File_XXXX   30-Sep                   9/30/2021              Fail 11:49 PM

     

    Can you please help me on this?

    Thanks!

  • Is this the final version, or are you going to continue gradually drip-feeding additional requirements?

    If so, please skip to the end and provide the final version now, to avoid wasting people's time.

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • Sorry, This is the final version.

  • Here is one way.

    DECLARE @x TABLE
    (
    FileName VARCHAR(50) NOT NULL
    ,CreatedDate DATE NOT NULL
    ,FileSendingDate DATE NOT NULL
    ,Status VARCHAR(50) NOT NULL
    );

    INSERT @x
    (
    FileName
    ,CreatedDate
    ,FileSendingDate
    ,Status
    )
    VALUES
    ('File_XXXX', '20210901', '20210901', 'Pass 02:52 PM')
    ,('File_XXXX', '20210902', '20210902', 'Pass 02:42 PM')
    ,('File_XXXX', '20210903', '20210903', 'Pass 01:22 PM')
    ,('File_XXXX', '20210906', '20210906', 'Pass 12:44 PM')
    ,('File_XXXX', '20210901', '20210930', 'Fail 11:41 PM')
    ,('File_XXXX', '20210930', '20210930', 'Fail 11:49 PM');

    WITH ordered
    AS (SELECT x.FileName
    ,x.CreatedDate
    ,x.FileSendingDate
    ,x.Status
    ,rn = ROW_NUMBER() OVER (PARTITION BY x.FileName
    ,x.CreatedDate
    ORDER BY x.FileSendingDate DESC
    )
    FROM @x x)
    SELECT ordered.FileName
    ,ordered.CreatedDate
    ,ordered.FileSendingDate
    ,ordered.Status
    FROM ordered
    WHERE ordered.rn = 1
    ORDER BY ordered.FileSendingDate
    ,ordered.CreatedDate;

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

Viewing 9 posts - 1 through 9 (of 9 total)

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