Retain 1 record for duplicated records

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Retain 1 record for duplicated records

Author

Topic

NewSQLMember
Starting Member

15 Posts

Posted - 2009-07-01 : 02:35:06

Hi,

Anyone can help me on how to solve in a query;

TableName : MyTable

Records in MyTable

Field1 Field2 Field3
------ ------ ------
101 101A AAA
102 102B BBB
103 103A AAA
104 104A AAA
105 105C CCC
...
...
...

I have millions of records, how can I select only the unique Field3? Display like these;

Field1 Field2 Field3
------ ------ ------
101 101A AAA
102 102B BBB
105 105C CCC
...
...
...

Thanks in Advance,
Ferdie

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 02:37:54

are you using SQL Server 2005 / 2008 ?

KH
[spoiler]Time is always against us[/spoiler]

NewSQLMember
Starting Member

15 Posts

Posted - 2009-07-01 : 02:41:00

Hi Khtan,

Im using 2005.

Regards,
Ferdie

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 02:42:28

[code]
select Field1, Field2, Field3
from (
select Field1, Field2, Field3,
row_no = row_number() over (partition by Field3 order by Field1, Field2)
from MyTable
) t
where t.row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

NewSQLMember
Starting Member

15 Posts

Posted - 2009-07-01 : 02:44:49

Hi Khtan,

Wow, genius. it work well. Many many thanks.

Regards,
Ferdie

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-01 : 06:55:38

See what you can do with row_number() function
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources