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

Remove Duplicated row on a iseries table Expand / Collapse
Author
Message
Posted Sunday, November 18, 2012 6:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, November 18, 2012 6:41 PM
Points: 1, Visits: 13
I have a table similar to below

Customer Referencedate card_number created_by
John 112233451 2008-02-01 48001796 Sen
John 112233451 2008-02-01 48001796 Sen
Eddy 123565654 2008-03-26 48001799 Mark
Mary 124578128 2009-10-27 48001850 Mark

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
Post #1386122
Posted Sunday, November 18, 2012 9:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:38 AM
Points: 5,589, Visits: 24,948
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



If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1386139
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse