x

Finding and returning a result set of supplicate rows.

I am trying to find possible duplicate purchase orders in the system and am looking for a query that will return the number of occurences that duplicate vendor id's occur,

then I want to return a result set the takes that reflects the vendor's data. like purchase_ordernum, total_amount, create_date.

I have the first part done. What's the best way to pull the rest of the data?

SELECT [VENDOR_ID],

COUNT ([VENDOR_ID]) AS NumOccurrences FROM [Database].[dbo].[PURCHASE_ORDER] GROUP BY [VENDOR_ID] HAVING (COUNT([VENDOR_ID]) > 1)

more ▼

asked May 23, 2012 at 03:51 PM in Default

technette gravatar image

technette
1.2k 86 99 109

do you want the purchase_ordernum, totoal_amount and create_date that correspond to those duplicates, or do you mean you want to include them in determining the duplication?
May 23, 2012 at 03:58 PM Kev Riley ♦♦

Thank you for responding Kev!

The purchase_ordernum, totoal_amount and create_date should correspond to those duplicates.
May 23, 2012 at 04:43 PM technette
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

At the simplest level you could do:

select vendor_id,purchase_ordernum, totoal_amount,create_date
from [Database].[dbo].[PURCHASE_ORDER]
where vendor_id in (
SELECT [VENDOR_ID]
FROM [Database].[dbo].[PURCHASE_ORDER] 
GROUP BY [VENDOR_ID] HAVING (COUNT([VENDOR_ID]) > 1))
more ▼

answered May 23, 2012 at 05:26 PM

Kev Riley gravatar image

Kev Riley ♦♦
53.9k 47 49 76

Thank you very Kev. This is perfect.
May 23, 2012 at 08:17 PM technette
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x14

asked: May 23, 2012 at 03:51 PM

Seen: 1754 times

Last Updated: May 23, 2012 at 08:17 PM