How to remove duplicate value from the rows in sql

Question

Post reply

How to remove duplicate value from the rows in sql

shasivashok

SSC Journeyman

Points: 79
More actions
October 26, 2021 at 3:33 pm
Go to Answer
#3944632

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

Phil Parkin

SSC Guru

Points: 246695

October 27, 2021 at 3:24 pm

Answer

#3945104

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 haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

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

ScottPletcher SSC Guru Points: 100948 More actions · Answer 1

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) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

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)

shasivashok SSC Journeyman Points: 79 More actions · Answer 3

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

Phil Parkin SSC Guru Points: 246695 More actions · Answer 4

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 haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

shasivashok SSC Journeyman Points: 79 More actions · Answer 5

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!

Phil Parkin SSC Guru Points: 246695 More actions · Answer 6

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 haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

shasivashok SSC Journeyman Points: 79 More actions · Answer 7

shasivashok

SSC Journeyman

Points: 79

October 27, 2021 at 3:01 pm

#3945094

Sorry, This is the final version.