SQL Help

JUMPURS · 15 Sep 2009 at 12:14

Hi,

I have the following query in order to check for duplicates in a table

Code:

SELECT MAX(ID) as PONumber FROM [PurchasesTemp]
GROUP BY PONumber
Having Count(*) > 1

The problem is, if there are multiple duplicates, it only returns the last one?

Is there a way i can change it so it will return all the duplicates?

Cheers

sist_si · 15 Sep 2009 at 12:33

could you do:
select ID as PONumber FROM [PurchasesTemp]
where ID = (SELECT MAX(ID) FROM [PurchasesTemp]
GROUP BY PONumber
Having Count(*) > 1)

SimonCHere · 15 Sep 2009 at 13:17

You're going to have to show us more of the table (purchasesTemp) as there isn't enough there to work with. We need something to group by to get the duplicates.

Assuming ID is a unique key. si's code wont work either I think.

SimonCHere · 15 Sep 2009 at 13:22

Try this

Code:

select pt.ID
from [PurchasesTemp] pt
Inner join (SELECT MAX(ID) ID1, [other]  FROM [PurchasesTemp]
               GROUP BY [other]
               Having Count([other]) > 1)  pt1
ON pt.[other] = pt1.[other]
and pt.ID < pt1.ID1

JUMPURS · 15 Sep 2009 at 13:43

Sorry, The ID is a unique number, but what is duplicates is PONumber.

In your code, what would i use for [other]?

rpstewart · 15 Sep 2009 at 14:26

Surely you need to start with the list of duplicate PONumbers then find all the IDs which relate to those PONumbers:

Code:

select a.id, b.PONumber 
from PurchasesTemp a
, (select PONumber, count(*) from PurchasesTemp group by PONumber having count(*) > 1) b
where a.PONumber=b.PONumber

(I'll leave you to translate from DB2 SQL to whatever you use)

JUMPURS · 15 Sep 2009 at 14:32

I think the issue is in the SELECT MAX(ID) part. I assume that is only giving me the ID which is the highest one and not all the ID's?

SimonCHere · 15 Sep 2009 at 14:34

JUMPURS said:
Sorry, The ID is a unique number, but what is duplicates is PONumber.

In your code, what would i use for [other]?

[other] would be PONumber then.

In your original post you have

SELECT MAX(ID) as PONumber

which aliases the ID column as "PONumber" so I thought there was only 1 column.

rpstewart's code is similar but it will show you all the rows for each duplicate, not just the duplicate rows which is what mine should do.

Code:

ID  PONUMBER
1   a
2   d
3   a
4   b
5   c
6   b
7   d
8   a

mine should give
1 (a)
3 (a)
4 (b)
2 (d)

rpstewart should give
1 (a)
3 (a)
8 (a)
4 (b)
6 (b)
2 (d)
7 (d)

Haircut · 15 Sep 2009 at 14:55

What database?
If Oracle or Sql Server (2005 or later) you can use an analytic query to do this pretty easily.

JUMPURS · 15 Sep 2009 at 15:10

Haircut said:
What database?
If Oracle or Sql Server (2005 or later) you can use an analytic query to do this pretty easily.

SQL 2005 mate.

sist_si · 15 Sep 2009 at 15:20

JUMPURS said:
Sorry, The ID is a unique number, but what is duplicates is PONumber.

In your code, what would i use for [other]?

Ah, yep. mine deffo won't work then.

If you only want the dup'ed rows, I'd prob do a little bit of working with a table var to work out my result set .

SimonCHere · 15 Sep 2009 at 15:25

My code or rpstewart's will give you what you want.

if you want to remove the duplicates, use my code in a delete, if you want to display all the records that are duplicated, use rpstewart's code.

Haircut · 15 Sep 2009 at 15:38

JUMPURS said:
SQL 2005 mate.

You should be able to use something like the example given here if you want then
http://articles.techrepublic.com.com/5100-10878_11-6080082.html#

It's for Oracle, but the query should be pretty much identical.
May be overkill for your needs, but is a nice way of doing efficient de-duplication.