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

Question

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

GElwood

Old Hand

Points: 349
More actions
December 22, 2010 at 9:01 am

#229000

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 .........

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

GElwood Old Hand Points: 349 More actions · Answer 2

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???

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

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[/url]

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]

_______________________________________________________________

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323504 More actions · Answer 4

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286573 More actions · Answer 5

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

_______________________________________________________________

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

WayneS SSC Guru Points: 95409 More actions · Answer 6

GElwood (12/22/2010)
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.

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
Author - SQL Server T-SQL Recipes

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

GElwood Old Hand Points: 349 More actions · Answer 7

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.??

24.8537677961.5OZ TOPPING CHOC FUDGE PACKET 24620 JHS PFG-Thoms Proestler Co 24.85