June 15, 2010 at 9:11 am
Hello,
I have duplicate records in the table with the column (productname,colorcode,categoryname,brandname,colorname), following below is the structure of my table, I want to write SELECT query so that it will generate resultset with all duplicates entry with above mentioned columns, can anybody please give me any suggestion how can i do this
SELECT [productid]
,[colornameid]
,[colorcode]
,[colorname]
,[available_qty]
,[committed_qty]
,[backorder_qty]
,[available_qty_temp]
,[wholesale_price]
,[price_paid]
,[psizeid]
,[psizename]
,[flag1]
,[productcategoryid]
,[categoryname]
,[productname]
,[brandid]
,[brandname]
,[cba]
,[Amazon]
,[test01]
,[FourTwenty]
,[test02]
,[yui]
,[UPC]
,[hotflag]
,[hotflagemail]
,[aodate]
FROM [dbo].[productscolortb_withsize]
June 15, 2010 at 9:22 am
this will give you the results featuring row_number(); use the inner portion of the query to see the raw results, the outer query returns just the duplicate items:
productname,colorcode,categoryname,brandname,colorname
SELECT * FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY productname,colorcode,categoryname,brandname,colorname ORDER BY productname,colorcode,categoryname,brandname,colorname)
AS RW,
[productid]
,[colornameid]
,[colorcode]
,[colorname]
,[available_qty]
,[committed_qty]
,[backorder_qty]
,[available_qty_temp]
,[wholesale_price]
,[price_paid]
,[psizeid]
,[psizename]
,[flag1]
,[productcategoryid]
,[categoryname]
,[productname]
,[brandid]
,[brandname]
,[cba]
,[Amazon]
,[test01]
,[FourTwenty]
,[test02]
,[yui]
,[UPC]
,[hotflag]
,[hotflagemail]
,[aodate]
FROM [dbo].[productscolortb_withsize]
) MyAlias
where RW > 1
Lowell
June 15, 2010 at 9:23 am
;WITH Dups
AS
(
SELECT productname, colorcode, categoryname, brandname, colorname
FROM [dbo].[productscolortb_withsize]
GROUP BY productname, colorcode, categoryname, brandname, colorname
HAVING COUNT(*) > 1
)
SELECT bs.*
FROM Dups d
JOIN [dbo].[productscolortb_withsize] bs
ON bs.productname =d.productname
AND bs.colorcode = d.colorcode
AND bs.categoryname = d.categoryname
AND bs.brandname = d.brandname
AND bs.colorname = d.colorname
June 15, 2010 at 9:52 am
I tried it but I am not getting correct result my problem is if values in [productname] and [colorcode] and [colorname] and [categoryname] and [brandname] matches with another row having [productname] and [colorcode] and [colorname] and [categoryname] and [brandname] then it should populate result in resultset for SELECT query
June 15, 2010 at 9:56 am
Both of the solutions givenm whould produce list of duplicates. If it's not what you wanted it is due to your question is unclear as posted.
Please provide table setup script, sample data population script and desired results of the required query.
June 15, 2010 at 10:09 am
Do you consider it rows to be duplicates if ANY of the columns match, or only if ALL of those columns match?
Post up a few sample rows and what you would expect the output to look like so everyone can understand your problem. A picture is worth a thousand rows.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 15, 2010 at 10:12 am
for eg.
[productname] = U3300/I
[categoryname] = GUCCI
[brandname] = Sunglasses
[colorcode] = CO3
[colorname] = black
if this exact matches with another row having same values then it should give result in resultset for SELECT query
June 15, 2010 at 10:22 am
And what is wrong with query I've posted? It does exactly that, if not show the example of data it misses.
If you don't want to waste yours and others time. Please provide the setup scripts as per:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 15, 2010 at 2:08 pm
sorry my data was bad in the rows so didnt get correct results, but this queires helped me a lot..
thanks you guys !!
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply