November 18, 2012 at 6:46 pm
I have a table similar to below
CustomerReferencedatecard_number created_by
John 1122334512008-02-0148001796Sen
John1122334512008-02-0148001796Sen
Eddy1235656542008-03-2648001799Mark
Mary1245781282009-10-2748001850Mark
From the above table I need to remove all duplicated rows leaving one row of each record on the table.i.e I need to have one record of customer ‘John’ on the table. The duplicated rows having same values on all columns and thus difficult to use max or min.
Thanks
November 18, 2012 at 9:35 pm
You could try something like:
|
CREATE TABLE #T(Date_Stamp DATETIME,KW1 DECIMAL(5,1), KW2 DECIMAL(5,1))
INSERT INTO #T
SELECT '12/10/2010', 5.3, 3.1 UNION ALL
SELECT '12/10/2010', 5.3, 3.1 UNION ALL
SELECT '12/9/2010', 4, 2 UNION ALL
SELECT '12/8/2010', 3, 1 UNION ALL
SELECT '12/7/2010', 7.4, 5 UNION ALL
SELECT '12/7/2010', 7.4, 5 UNION ALL
SELECT '12/7/2010', 7.4, 5
with cte
as (select row_number() over(partition by Date_Stamp,KW1,KW2 order by Date_Stamp) as rn,
KW1,KW2
from #T)
SELECT * FROM cte
duplicate entries would have a row_number greater than 1
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply