|
|
|
SSC-Enthusiastic
 
Group: General Forum Members
Last Login: Monday, March 15, 2010 12:33 PM
Points: 144,
Visits: 60
|
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, February 17, 2010 6:59 AM
Points: 43,
Visits: 157
|
|
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 ' '.
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 21,
Visits: 114
|
|
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;
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, February 17, 2010 6:59 AM
Points: 43,
Visits: 157
|
|
| That worked, thanks for the help.
|
|
|
|
|
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
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 21,
Visits: 114
|
|
Nice script Tom, we've used it a couple of times already!
Thanks
Dave
|
|
|
|
|
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)
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, March 15, 2010 12:33 PM
Points: 144,
Visits: 60
|
|
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
|
|
|
|
