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

avatar image

technette
1.4k 100 113 120

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

avatar image

Kev Riley ♦♦
64.2k 48 62 81

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.

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:

x18

asked: May 23, 2012 at 03:51 PM

Seen: 3105 times

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

Copyright 2016 Redgate Software. Privacy Policy