InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 08:33:26 GMT20http://www.sqlservercentral.com/Forums/Topic1038329-391-1.aspx[quote][b]Lowell (12/22/2010)[/b][hr]based on Sean's post, i think if you join the grouping back to the original table, you can at least see the dupes :[code]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 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[/code][/quote]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.85Thu, 23 Dec 2010 07:12:17 GMTGElwoodhttp://www.sqlservercentral.com/Forums/Topic1038329-391-1.aspx[quote][b]GElwood (12/22/2010)[/b][hr]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.[/quote]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.Wed, 22 Dec 2010 18:47:36 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1038329-391-1.aspxExcellent! I can tell it is almost quitting time....for the rest of the year. :-PWed, 22 Dec 2010 14:17:55 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1038329-391-1.aspxbased on Sean's post, i think if you join the grouping back to the original table, you can at least see the dupes :[code]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 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[/code]Wed, 22 Dec 2010 14:15:34 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1038329-391-1.aspxJeff 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.[url=http://www.sqlservercentral.com/articles/T-SQL/63681/]Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url][url=http://www.sqlservercentral.com/articles/Crosstab/65048/]Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]Wed, 22 Dec 2010 14:13:42 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1038329-391-1.aspx[quote][b]Sean Lange (12/22/2010)[/b][hr]try something like this.[code]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 [/code][/quote]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???Wed, 22 Dec 2010 13:58:16 GMTGElwoodhttp://www.sqlservercentral.com/Forums/Topic1038329-391-1.aspxtry something like this.[code]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 [/code]Wed, 22 Dec 2010 13:29:00 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1038329-391-1.aspxI 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 #MXselect 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 flagfrom opco_item_tbl i inner join cd_item_tbl ii on i.item_num = ii.item_nbrinner 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_nbrinner join bid_detail_tbl bd on i.item_num = bd.item_num and i.opco_num = bd.opco_numinner join bid_customer_tbl bc on bd.bid_num = bc.bid_num and bd.opco_num = bc.opco_numinner join opco_customer_tbl c on bc.customer_ident = c.customer_identinner join pfg_opco_tbl o on i.opco_num = o.opco_numwhere i.item_num in Lst.Lst35)and chain_code = '*ABC'group by .........Wed, 22 Dec 2010 09:01:31 GMTGElwood