SQL 2008 R2 Duplications

I have the following rows in SQL

DATE      date      Checked
APPLICANT      varchar(MAX)      Checked
REMARKS      varchar(MAX)      Checked
[CARRIER TYPE]      varchar(MAX)      Checked
QUANTITY      numeric(18, 2)      Checked
COUNT      numeric(18, 2)      Checked
CONTAINER      varchar(MAX)      Checked
GRADE      varchar(50)      Checked
[CERTIFICATE NUMBER]      numeric(18, 0)      Checked
[QUANTITY THOUSAND WEIGHT]            Checked
[QUANITIY HUNDRED WEIGHT]            Checked
TempID      int      Unchecked

I have about 700 duplication s that happened today.  I need to list them and delete them.

How can I go about doing this??
Curtis LongAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
To identify the duplicates, assuming we're talking about allcolumns, give the below T-SQL a whirl, replacing the obvious
SELECT column1, column2, etc, columnN, COUNT(ColumnN)
FROM YourTable
GROUP BY column1, column2, etc, columnN
HAVING COUNT(ColumnN) > 1

Open in new window

To delete them, you'll need to spell out the logic behind which one to delete.  Is there an identity field somewhere, delete the first one, etc.
0
Curtis LongAuthor Commented:
Is there a way to say select these columns if column 1 column 2 column 3 and column 4 are the same??
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Yes, in my column1..columnN example, replace it with 1..4
SELECT column1, column2, column3, column4, COUNT(Column4)
FROM YourTable
GROUP BY column1, column2, column3, column4
HAVING COUNT(ColumnN) > 1

Open in new window

0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Curtis LongAuthor Commented:
So I type the following for my Query

SELECT   DATE, APPLICANT, REMARKS, [CARRIER TYPE], QUANTITY, COUNT, CONTAINER, GRADE, [CERTIFICATE NUMBER], [QUANTITY THOUSAND WEIGHT],
                      [QUANITIY HUNDRED WEIGHT], TempID, COUNT ([CERTIFICATE NUMBER]..COUNT)
FROM         INSPECTION_v2
GROUP BY DATE, APPLICANT, REMARKS, [CARRIER TYPE], QUANTITY, COUNT, CONTAINER, GRADE, [CERTIFICATE NUMBER], [QUANTITY THOUSAND WEIGHT],
                      [QUANITIY HUNDRED WEIGHT], TempID

having COUNT ([CERTIFICATE NUMBER]..COUNT) > 1

I get this error:

Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "CERTIFICATE NUMBER..COUNT" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "CERTIFICATE NUMBER..COUNT" could not be bound.

Any yes, I am a noob!  ;-)
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>having COUNT ([CERTIFICATE NUMBER]..COUNT) > 1
Lose the dot dot COUNT
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Any yes, I am a noob!  ;-)
That's okay, I'm a sucker for points.

btw I have an article out there on SQL Server GROUP BY expressions that deals with count and having that may be a good read
0
Curtis LongAuthor Commented:
I will definitely read that.

Now, I am really confusing myself and thanks for your patience!!

If I want to list all the columns if the value in the column QUANTITY and in column [CERTIFICATE NUMBER] are duplication, what would I replace in your sample query??


Yes, in my column1..columnN example, replace it with 1..4

SELECT column1, column2, column3, column4, COUNT(Column4)
FROM YourTable
GROUP BY column1, column2, column3, column4
HAVING COUNT(ColumnN) > 1
0
SharathData EngineerCommented:
To display the duplicates
SELECT QUANTITY,[CERTIFICATE NUMBER] , COUNT(*)
FROM YourTable
GROUP BY QUANTITY,[CERTIFICATE NUMBER] 
HAVING COUNT(*) > 1

Open in new window


To delete them
;with cte as (select *,count(*) over (partition by QUANTITY,[CERTIFICATE NUMBER]) cnt from YourTable)
delete from cte where cnt > 1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Curtis LongAuthor Commented:
That works PERFECTLY!!

Actually I went from typing a big long "Im so confused" message to looking a couple things up and learning something.  :-)

One thing I AM confused about.

I tried this out on my test data base.

The query to display duplicates returned 772 items.

When I ran the delete query it deleted around 2100.

Why would I get different numbers??

Anyway, thanks so much for your help!!
0
SharathData EngineerCommented:
772 distinct combinations of QUANTITY,[CERTIFICATE NUMBER] have duplicates. And there are a total of 2100 duplicate records.

For example,
QUANTITY [CERTIFICATE NUMBER]
100           A
100      A
200      B
100      A
200      B
300      C

In this example, distinct combinations are 2 where as duplicate records are 5.
0
Curtis LongAuthor Commented:
So sorry!!  I thought I had awarded points yesterday.

Thanks for everything!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.