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 Duplicates from a Table using specific columns Expand / Collapse
Author
Message
Posted Saturday, March 23, 2013 10:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 24, 2013 9:22 PM
Points: 1, Visits: 4
Hi Gurus,

I am new to Teradata and working on a requirement.

Below is the table

Col1 Col2 Col3 Col4

A Y 1 Z
B Y 2 Q
C Y 3 R
A Y 4 Z
A Y 5 Z


Output as:

Col1 Col2 Col3 Col4

A Y 1 Z
B Y 2 Q
C Y 3 R


Please guide me resolving this issue.

Thanks,
LG




Post #1434662
Posted Sunday, March 24, 2013 9:14 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 6, 2014 12:39 PM
Points: 376, Visits: 172
if you're working on a huge amount of data I think the most optimized way would be to use the row)number function. This allows you to partition the values you need to determine to be unique.

In the query I have written below you can see that by partitioning you are assigning unique values to those columns in the partition. If you come across two values that are the same the partition will assign a new number for that value. The outer query where clause is important in this case because by choosing one you are choosing to only select those that are non duplicated.

njoy!



CREATE TABLE #temp
(
Col1 varchar(100),
Col2 varchar(100) ,
Col3 varchar(100) ,
Col4 varchar(100)
)


INSERT INTO #temp
VALUES ('A', 'Y', '1', 'Z'),
('B', 'Y', '2', 'Q'),
('C', 'Y', '3', 'R'),
('A', 'Y', '4', 'Z'),
('A', 'Y', '5', 'Z')


SELECT Col1,
Col2,
Col3,
Col4 FROM (
select ROW_NUMBER()OVER (PARTITION BY Col1 order by COL1) ROWID,
Col1,
Col2,
Col3,
Col4
FROM #temp)LL
where ROWID = 1
Post #1434677
Posted Sunday, March 24, 2013 5:47 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
A Y 1 Z


What is the rule for leaving this particular record, not any of the others with "A Y"?
Post #1434711
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse