Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server 2005 Remove Dups - CTE Expand / Collapse
Author
Message
Posted Thursday, March 20, 2008 8:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 30, 2014 8:00 PM
Points: 150, Visits: 125
Comments posted to this topic are about the item SQL Server 2005 Remove Dups - CTE
Post #472282
Posted Friday, May 9, 2008 7:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 2, 2014 7:46 AM
Points: 46, Visits: 187
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
Posted Friday, May 9, 2008 8:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 12:13 PM
Points: 41, Visits: 313
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
Posted Friday, May 9, 2008 8:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 2, 2014 7:46 AM
Points: 46, Visits: 187
That worked, thanks for the help.
Post #497934
Posted Saturday, May 10, 2008 1:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Saturday, May 10, 2008 5:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 12:13 PM
Points: 41, Visits: 313
Nice script Tom, we've used it a couple of times already!

Thanks

Dave
Post #498346
Posted Saturday, October 4, 2008 1:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 25, 2013 9:45 AM
Points: 4, Visits: 8
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
Posted Monday, October 6, 2008 12:30 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 30, 2014 8:00 PM
Points: 150, Visits: 125
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
Posted Tuesday, June 7, 2011 7:24 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, September 8, 2014 5:25 PM
Points: 416, Visits: 534
Very nice. much cleaner than the temp table method...thx
Post #1120994
Posted Friday, January 31, 2014 2:17 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 11:02 AM
Points: 56, Visits: 94
Thank you too, you just saved me a whole rewrite of a program.

G
Post #1536975
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse