I have a table with about 300 entires in it and I am trying to create a query that only selects the 33 records in the table that are duplicated.

These are my 3 column headings

TOrder_NO
TPart_NO
Qty

In 33 records TOrder_NO and TPart_NO are the same how can i run a query to just select these 33 records.

Cheers

SELECT torder_no,tpart_no FROM orders GROUP BY torder_no,tpart_no HAVING count(*)>1;
Supposedly two records are equal if Record1.TOrder_NO = Record2.TOrder_NO AND Record1.TPart_NO = Record2.TPart_NO

How did you found you have 33 duplicated records?

Hope this helps!

Cheers for the reply that works but I need the Qty to be listed in the results but it dosen't affect the duplication. So it should just publish the QTY results, but it shouldn't affect the query.

Cheers

Simon,

What about :

SELECT torder_no,tpart_no, Qty FROM orders GROUP BY torder_no,tpart_no HAVING count(*)>1;

You could also try:

SELECT t1.torder_no, t1.tpart_no, t1.Qty FROM orders AS t1, orders AS t2 WHERE t1.order_no = t2.order_no AND t1.id <> t2.id

I am assuming that you also have a primary key (id) in the orders table.

Once you delete all the duplicates you may want to put a unique constraint on the order_no column to avoid duplicates in future.

So if you have got the following data:
order_no part_no qty
1 1 20
1 1 18
what should be the correct output - two rows or one row; in case of one row, what would be the criteria for selecting the quantity e.g. random, lowest, highest, other?

Best Regards

Cheers for the help so far, this is where am up to. I can select the items that are duplicated but when i try to select all 3 fields from my table it dosen't work.

Here is my query

SELECT Torder_NO, TPart_NO
FROM dbo.TestingGSS
GROUP BY Torder_NO, TPart_NO
HAVING (COUNT(*) > 1)

But I have a column called TOrder_Qty that i want included in the results but it shouldn't affect the crietria of the query. So far I have tried this numerous ways and it dosen't work.


Cheers for any help