finding duplicate columns

Morpheus

This is the basic table below....

product location quantity
1 AA1 123
2 AV2 22
1 AA1 0
3 DD2 65
4 FS1 53
1 AA1 311


I need to locate all the records which have the same Product and Location values. basically a select which would find the type of records that have product 1 location AA1 above, there are 3 of these.

So far, I can find all records with a product id the same using this...

select
product,
count(product) as numOcc
from
mytable
group by
product
having
(count(product) >1)

Can anyone help?

JazzyJ

This should work but I'm sure there's better way:

SELECT
	*
FROM
	TEST A
WHERE EXISTS
	(SELECT
		NULL
	FROM
		TEST B
	WHERE
		A.PRODUCT = B.PRODUCT
	AND	A.LOCATION = B.LOCATION
	GROUP BY
		PRODUCT, LOCATION
	HAVING
		COUNT(*) > 1)

bonkey

Alternately, you can use a join :

select mytable.*
from mytable
inner join ( select product
                  , count(product) as numOcc
               from mytable
               group by
                    product
               having
                    count(product) > 1
           ) as duplicates
on mytable.product = duplicates.product           

Joining the table to the original grouped info, however, will give the desired result.

Or you could do it using the following (although I'm not sure all SQL implementations allow you to use a HAVING clause thats not in the SELECT clause) :

select  *
  from  mytable
  where product in ( select product
                       from mytable
                       group by
                            product
                       having
                            count(product) > 1
                   )

JazzyJ

Bonkey,

You're forgetting to take into account of the location as well, i.e. its duplicate product and location, rather than just products.

From checking the execution plans of the exist vs join queries on this table (with appropriate indexes added) the join method is slightly quicker. Dunno what they'd be like for larger amounts of data though.

J.

bonkey

JazzyJ wrote:

Bonkey,

You're forgetting to take into account of the location as well, i.e. its duplicate product and location, rather than just products.

You're right. I based my queries on that of the OP, rather than on the problem he described.

Duh, me.

From checking the execution plans of the exist vs join queries on this table (with appropriate indexes added) the join method is slightly quicker. Dunno what they'd be like for larger amounts of data though.

For larger amounts of data, I'd hope there were suitable indexes....which would completely change the game.

It'd most likely also be DB-dependant as to which optimizes best at a guess.

Morpheus

Yes, should have simply said i need to retrieve all records from a table which have 2 duplicate columns!!!

Have tried this so far,

SELECT column1, column2
FROM myTable
GROUP BY column1, column2
HAVING COUNT(*)>1

Does it look right?

Havent yet tested your solutions but i will test them later this morning.

Thanks!