SQL Server 2005 Remove Dups - CTE

  • tomeh52-508672

    SSC Veteran

    Points: 242

    Comments posted to this topic are about the item SQL Server 2005 Remove Dups - CTE

  • Bob Shaw

    Right there with Babe

    Points: 787

    im getting an error on the With Dups portion of the code.

    With Dups as

    (

    ????select *, row_number() over

    (partition by Product_Code order by Product_Code) as RowNum

    ????from #prod

    )

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 5

    Incorrect syntax near '?'.

  • DMarvez

    Ten Centuries

    Points: 1181

    Try running these two statements at the same time:

    With Dups as

    (

    select *, row_number() over (partition by Product_Code order by Product_Code) as RowNum from #prod

    )

    Delete from Dups where rownum > 1;

  • Bob Shaw

    Right there with Babe

    Points: 787

    That worked, thanks for the help.

  • Chandrasekar.Vijayakumar

    Grasshopper

    Points: 20

    Hi ,

    CREATE TABLE #prod(Product_Code varchar(10),Product_Name varchar(100))

    INSERT INTO #prod(Product_Code, Product_Name)

    VALUES ('123','Product_1')

    INSERT INTO #prod(Product_Code, Product_Name)

    VALUES ('234','Product_2')

    INSERT INTO #prod(Product_Code, Product_Name)

    VALUES ('345','Product_3')

    INSERT INTO #prod(Product_Code, Product_Name)

    VALUES ('345','Product_3')

    INSERT INTO #prod(Product_Code, Product_Name)

    VALUES ('456','Product_4')

    INSERT INTO #prod(Product_Code, Product_Name)

    VALUES ('567','Product_5')

    INSERT INTO #prod(Product_Code, Product_Name)

    VALUES ('678','Product_6')

    INSERT INTO #prod(Product_Code, Product_Name)

    VALUES ('789','Product_7')

    SELECT * FROM #prod;

    With Dups as

    (

    select *, row_number() over (partition by Product_Code order by Product_Code) as RowNum from #prod

    )

    Delete from Dups where RowNum > 1;

    In RowNum r and n was written in lower case for the delete command i changed it

    Chandru.V

  • DMarvez

    Ten Centuries

    Points: 1181

    Nice script Tom, we've used it a couple of times already!

    Thanks

    Dave

  • vj.raj.1979

    SSC Rookie

    Points: 34

    Hi its good seeing that logic, but i have scenario

    c1 c2 c3

    1 a a1

    1 a b1

    1 a c1

    2 b a7

    2 b b4

    3 c d4

    3 c b5

    3 c v5

    4 d u7

    in this data provided , i would like to pick up the first set of values

    ex: 1 a a1

    2 b a7

    3 c d4

    4 d u7

    should be the out put( in sql server)

  • tomeh52-508672

    SSC Veteran

    Points: 242

    Two things to address 1st.

    1) You would have to make an assumption about the column you are sorting on. In this case c3, correct? That would be sorted based on a character sort, not numeric. Either that is ok or you would need to somehow parse that column to sort it differently.

    2) How did you get "3 c d4" and not "3 c b5" back in your ideal recordset?

    Here’s what I can up with. Hope that helps.

    Tom

    ----DROP TABLE #temp

    --CREATE TABLE #temp(

    -- c1 INT,

    -- c2 VARCHAR(1),

    -- c3 VARCHAR(6)

    --)

    --

    --

    --INSERT INTO #temp(c1,c2,c3)

    --VALUES(1,'a','a1')

    --

    --INSERT INTO #temp(c1,c2,c3)

    --VALUES(1,'a','b1')

    --

    --INSERT INTO #temp(c1,c2,c3)

    --VALUES(1,'a','c1')

    --

    --INSERT INTO #temp(c1,c2,c3)

    --VALUES(1,'a','a11')

    --

    --INSERT INTO #temp(c1,c2,c3)

    --VALUES(1,'a','a9')

    --

    --INSERT INTO #temp(c1,c2,c3)

    --VALUES(2,'b','a7')

    --

    --INSERT INTO #temp(c1,c2,c3)

    --VALUES(2,'b','b4')

    --

    --INSERT INTO #temp(c1,c2,c3)

    --VALUES(3,'c','d4')

    --

    --INSERT INTO #temp(c1,c2,c3)

    --VALUES(3,'c','b5')

    --

    --INSERT INTO #temp(c1,c2,c3)

    --VALUES(3,'c','v5')

    --

    --INSERT INTO #temp(c1,c2,c3)

    --VALUES(4,'d','u7')

    SELECT x.c1,

    x.c2,

    x.c3

    FROM (SELECT c1,

    c2,

    c3,

    row_number() OVER(PARTITION BY c1,c2 ORDER BY c3) AS rowNum

    FROM #temp)x

    WHERE x.rowNum = 1

  • Bill Anton

    SSCrazy

    Points: 2407

    Very nice. much cleaner than the temp table method...thx

    Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com

  • Gillian_Pappas2002

    SSCommitted

    Points: 1604

    Thank you too, you just saved me a whole rewrite of a program.

    G

Viewing 10 posts - 1 through 10 (of 10 total)

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