Duplicate records in the table

  • 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]

  • 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


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

  • ;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

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • 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

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

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • 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

  • 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

  • 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/

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • 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