SQL Server 2005 Remove Dups

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Tom Huneke » SQL Server 2005 Remove Dups - CTE

SQL Server 2005 Remove Dups - CTE

Rate Topic

Display Mode

Topic Options

Author

Message

tomeh52-508672

Posted Thursday, March 20, 2008 8:20 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, December 16, 2012 4:48 AM
Points: 150, Visits: 118

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

Post #472282

Bob Shaw

Posted Friday, May 09, 2008 7:37 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, March 07, 2013 11:34 AM
Points: 45, Visits: 174

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

Post #497868

DMarvez

Posted Friday, May 09, 2008 8:14 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:41 PM
Points: 40, Visits: 265

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;

Post #497920

Bob Shaw

Posted Friday, May 09, 2008 8:30 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, March 07, 2013 11:34 AM
Points: 45, Visits: 174

That worked, thanks for the help.

Post #497934

Chandrasekar.Vijayakumar

Posted Saturday, May 10, 2008 1:07 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, July 10, 2008 3:22 AM
Points: 2, Visits: 13

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

Post #498333

DMarvez

Posted Saturday, May 10, 2008 5:57 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:41 PM
Points: 40, Visits: 265

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

Thanks

Dave

Post #498346

vj.raj.1979

Posted Saturday, October 04, 2008 1:55 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, November 08, 2008 4:28 AM
Points: 4, Visits: 6

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)

Post #580697

tomeh52-508672

Posted Monday, October 06, 2008 12:30 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, December 16, 2012 4:48 AM
Points: 150, Visits: 118

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

Post #581309

byoBI

Posted Tuesday, June 07, 2011 7:24 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:45 PM
Points: 415, Visits: 487

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

Post #1120994

« Prev Topic | Next Topic »

Permissions