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: 246965

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;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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: 101105 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004363 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: 246965 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;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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: 246965 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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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.