Identify duplicate records where all fields, except one field, are the same.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Identify duplicate records where all fields,...

Identify duplicate records where all fields, except one field, are the same.

Rate Topic

Display Mode

Topic Options

Author

Message

GElwood

Posted Wednesday, December 22, 2010 9:01 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 16, 2011 6:11 AM
Points: 7, Visits: 42

I have a temp table with 9 fields.( Customer number, size, description, brand, etc.) The table also has a field call bid_price, if two records are identical except for bid_price, I what to flag those records some way so I know I have multiple prices for the same item.

CREATE TABLE #MX (
item_num int
,pack int
,item_size varchar(10)
,old_desc1 varchar(50)
,manufacturer_item_nbr varchar(25)
,brand_name varchar(25)
,opco_desc varchar(50)
,bid_price_old money
,flag int )

INSERT INTO #MX
select i.item_num
,i.pack
,i.item_size
,bi.old_desc1
,ii.manufacturer_item_nbr
,b.brand_name
,o.opco_desc
,bd.bid_price_old
,0 as flag
from opco_item_tbl i
inner join cd_item_tbl ii on i.item_num = ii.item_nbr
inner join cd_base_item_tbl bi on ii.base_item_nbr = bi.base_item_nbr
inner join cd_brand_tbl b on bi.brand_nbr = b.brand_nbr
inner join bid_detail_tbl bd on i.item_num = bd.item_num and i.opco_num = bd.opco_num
inner join bid_customer_tbl bc on bd.bid_num = bc.bid_num and bd.opco_num = bc.opco_num
inner join opco_customer_tbl c on bc.customer_ident = c.customer_ident
inner join pfg_opco_tbl o on i.opco_num = o.opco_num
where
i.item_num in Lst.Lst35
)
and
chain_code = '*ABC'
group by .........

Post #1038329

Sean Lange

Posted Wednesday, December 22, 2010 1:29 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 8,620, Visits: 8,261

try something like this.

select item_num, pack, item_size, old_desc1, manufacturer_item_nbr, brand_name, opco_desc, flag, COUNT(distinct bid_price_old) as bidCount
from #mx
group by item_num, pack, item_size, old_desc1, manufacturer_item_nbr, brand_name, opco_desc, flag

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1038472

GElwood

Posted Wednesday, December 22, 2010 1:58 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 16, 2011 6:11 AM
Points: 7, Visits: 42

Sean Lange (12/22/2010)

try something like this.

select item_num, pack, item_size, old_desc1, manufacturer_item_nbr, brand_name, opco_desc, flag, COUNT(distinct bid_price_old) as bidCount
from #mx
group by item_num, pack, item_size, old_desc1, manufacturer_item_nbr, brand_name, opco_desc, flag

This is good the query returns several records with a count of 2 (duplicate bid prices) but..... I also need to be able to see the duplicate prices. It would be nice if i could return a field with both prices in one field like: $24.15 - $24.95. Is this possible???

Post #1038482

Sean Lange

Posted Wednesday, December 22, 2010 2:13 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 8,620, Visits: 8,261

Jeff Moden has written some fantastic article on this site about doing that sort of thing. I think you would find his articles can get you the exactly what you need. It will be an excellent learning experience too.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1038494

Lowell

Posted Wednesday, December 22, 2010 2:15 PM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:29 PM
Points: 11,645, Visits: 27,738

based on Sean's post, i think if you join the grouping back to the original table, you can at least see the dupes :

SELECT #mx.bid_price_old, #mx.* FROM #mx
LEFT OUTER JOIN (
                 select item_num, pack, item_size, old_desc1, manufacturer_item_nbr, brand_name, opco_desc, flag, 
                 COUNT(distinct bid_price_old) as bidCount
                 from #mx
                 group by item_num, pack, item_size, old_desc1, manufacturer_item_nbr, brand_name, opco_desc, flag )
myAlias 
 ON   #mx.item_num = myAlias.item_num
 AND   #mx.pack = myAlias.pack
 AND   #mx.item_size = myAlias.item_size
 AND   #mx.old_desc1 = myAlias.old_desc1
 AND   #mx.manufacturer_item_nbr = myAlias.manufacturer_item_nbr
 AND   #mx.brand_name = myAlias.brand_name
 AND   #mx.opco_desc = myAlias.opco_desc
 AND   #mx.flag = myAlias.flag

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1038497

Sean Lange

Posted Wednesday, December 22, 2010 2:17 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 8,620, Visits: 8,261

Excellent! I can tell it is almost quitting time....for the rest of the year. Tongue

Post #1038500

WayneS

Posted Wednesday, December 22, 2010 6:47 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:12 AM
Points: 6,370, Visits: 8,235

GElwood (12/22/2010)

This sounds like you should be able to use the DENSE_RANK() function (partitioned by the first nine columns, ordered by the bid_price column) to get you the base information. Duplicates would be where the DENSE_RANK() returns a number > 1.

Wayne
Microsoft Certified Master: SQL Server 2008

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Post #1038573

GElwood

Posted Thursday, December 23, 2010 7:12 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 16, 2011 6:11 AM
Points: 7, Visits: 42

Lowell (12/22/2010)

based on Sean's post, i think if you join the grouping back to the original table, you can at least see the dupes :

SELECT #mx.bid_price_old, #mx.* FROM #mx
LEFT OUTER JOIN (
                 select item_num, pack, item_size, old_desc1, manufacturer_item_nbr, brand_name, opco_desc, flag, 
                 COUNT(distinct bid_price_old) as bidCount
                 from #mx
                 group by item_num, pack, item_size, old_desc1, manufacturer_item_nbr, brand_name, opco_desc, flag )
myAlias 
 ON   #mx.item_num = myAlias.item_num
 AND   #mx.pack = myAlias.pack
 AND   #mx.item_size = myAlias.item_size
 AND   #mx.old_desc1 = myAlias.old_desc1
 AND   #mx.manufacturer_item_nbr = myAlias.manufacturer_item_nbr
 AND   #mx.brand_name = myAlias.brand_name
 AND   #mx.opco_desc = myAlias.opco_desc
 AND   #mx.flag = myAlias.flag

This looks like it has potential but is still not showing the 2 different bid prices? For this record the bid prices should be 24.85 and 21.46.??

[b]24.85 37677 96 1.5OZ TOPPING CHOC FUDGE PACKET 24620 JHS PFG-Thoms Proestler Co 24.85

Post #1038774

« Prev Topic | Next Topic »

Permissions