Identify duplicate records where all fields, except one field, are the same.
-
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 .........
-
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 bidCountfrom #mxgroup 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/ -
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 bidCountfrom #mxgroup by item_num, pack, item_size, old_desc1, manufacturer_item_nbr, brand_name, opco_desc, flagThis 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???
-
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/ -
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 #mxLEFT OUTER JOIN (select item_num, pack, item_size, old_desc1, manufacturer_item_nbr, brand_name, opco_desc, flag,COUNT(distinct bid_price_old) as bidCountfrom #mxgroup by item_num, pack, item_size, old_desc1, manufacturer_item_nbr, brand_name, opco_desc, flag )myAliasON #mx.item_num = myAlias.item_numAND #mx.pack = myAlias.packAND #mx.item_size = myAlias.item_sizeAND #mx.old_desc1 = myAlias.old_desc1AND #mx.manufacturer_item_nbr = myAlias.manufacturer_item_nbrAND #mx.brand_name = myAlias.brand_nameAND #mx.opco_desc = myAlias.opco_descAND #mx.flag = myAlias.flagLowell
--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! -
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/ -
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 -
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 #mxLEFT OUTER JOIN (select item_num, pack, item_size, old_desc1, manufacturer_item_nbr, brand_name, opco_desc, flag,COUNT(distinct bid_price_old) as bidCountfrom #mxgroup by item_num, pack, item_size, old_desc1, manufacturer_item_nbr, brand_name, opco_desc, flag )myAliasON #mx.item_num = myAlias.item_numAND #mx.pack = myAlias.packAND #mx.item_size = myAlias.item_sizeAND #mx.old_desc1 = myAlias.old_desc1AND #mx.manufacturer_item_nbr = myAlias.manufacturer_item_nbrAND #mx.brand_name = myAlias.brand_nameAND #mx.opco_desc = myAlias.opco_descAND #mx.flag = myAlias.flagThis 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
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply