SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Tom Huneke / SQL Server 2005 Remove Dups - CTE / Latest Posts

RE: SQL Server 2005 Remove Dups - CTE

iPolvo — Tue, 07 Jun 2011 07:24:36 GMT

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

RE: SQL Server 2005 Remove Dups - CTE

tomeh52-508672 — Mon, 06 Oct 2008 12:30:46 GMT

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.c3FROM (SELECT c1, c2, c3, row_number() OVER(PARTITION BY c1,c2 ORDER BY c3) AS rowNum FROM #temp)xWHERE x.rowNum = 1

RE: SQL Server 2005 Remove Dups - CTE

vj.raj.1979 — Sat, 04 Oct 2008 01:55:39 GMT

Hi its good seeing that logic, but i have scenarioc1 c2 c31 a a11 a b11 a c12 b a72 b b43 c d43 c b53 c v54 d u7in 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 u7should be the out put( in sql server)

RE: SQL Server 2005 Remove Dups - CTE

DMarvez — Sat, 10 May 2008 05:57:59 GMT

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

RE: SQL Server 2005 Remove Dups - CTE

Chandrasekar.Vijayakumar — Sat, 10 May 2008 01:07:07 GMT

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)[b]Delete from Dups where RowNum > 1;[/b]In RowNum r and n was written in lower case for the delete command i changed itChandru.V

RE: SQL Server 2005 Remove Dups - CTE

Bob Shaw — Fri, 09 May 2008 08:30:43 GMT

That worked, thanks for the help.

RE: SQL Server 2005 Remove Dups - CTE

DMarvez — Fri, 09 May 2008 08:14:17 GMT

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;

RE: SQL Server 2005 Remove Dups - CTE

Bob Shaw — Fri, 09 May 2008 07:37:26 GMT

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 3Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 5Incorrect syntax near ' '.

SQL Server 2005 Remove Dups - CTE

tomeh52-508672 — Thu, 20 Mar 2008 08:20:20 GMT

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