Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
kalyan.cse05
Yak Posting Veteran
74 Posts |
Posted - 2014-02-18 : 04:11:23
|
How can i keep only one record from duplicate entry. ExampleCOLUMN1 COLUMN2 COLUMN3 COLUMN4AAA 121 -1 2AAA 121 -1 3AAA 121 -1 4Here i want to keep only the top record and want to delete other 2 rows. Can someone plz helpkalyan Ashis Dey |
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2014-02-18 : 05:22:36
|
So you want to keep the row with the lowest value in column4 and col1 to col3 are same values?delete from(select row_number() over(partition by columnn1, column2, column3 order by column4 asc)rn,* from YourTable)dtwhere rn > 1 Too old to Rock'n'Roll too young to die. |
|
|
kalyan.cse05
Yak Posting Veteran
74 Posts |
Posted - 2014-02-18 : 05:50:57
|
Thanks a lot for your helpkalyan Ashis Dey |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2014-02-20 : 04:25:32
|
CREATE TABLE #TAble1 (NAME VARCHAR(10),Id INT,ID1 INT,ID2 INT)INSERT INTO #TAble1(NAME ,Id ,ID1 ,ID2 ) VALUES ('AAA', 121, 1 ,2)INSERT INTO #TAble1 (NAME ,Id ,ID1 ,ID2 ) VALUES ('AAA', 121, 1, 3)INSERT INTO #TAble1 (NAME ,Id ,ID1 ,ID2 ) VALUES ('AAA', 121,1, 4);WITH CTE AS (Select NAME,Id,ID1,ID2,ROW_NUMBER()OVER (PARTITION BY ID ORDER BY ID )AS RN FROM #TAble1 t )DELETE from CTE WHERE RN = 1select * from #TAble1P.V.P.MOhan |
|
|
maunishq
Yak Posting Veteran
71 Posts |
Posted - 2014-02-21 : 14:22:02
|
@mohan123: It should be RN>1.!!_(M)_!! |
|
|
|
|
|
|
|