one record for duplicate entries

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

SQL Server 2008 Forums

Transact-SQL (2008)

one record for duplicate entries

Author

Topic

spunkiegirl
Starting Member

9 Posts

Posted - 2014-05-04 : 06:06:18

Good Morning,

I have a table that looks like the below: I want to return only one of the CustNmbr records.

CustNmbr Code UniqueID
10058 TM 1
10058 DIS 2
10058 REP-BANC 3
10059 TOS 4
10059 COMBINE 5
10059 REP-BANC 6
10076 TOS 7
10076 REP-BANC 8
10076 REP 9
10099 TM 10
10099 REDO 11

So I want my result set to look like the below:
10058 TM 1
10059 TOS 4
10076 TOS 7
10099 TM 10

Thank you!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-04 : 08:01:04

[code]select *
from
(
select *, rn = row_number() over (partition by CustNmbr order by UniqueID)
from CustNmbr
) d
where d.rn = 1[/code]

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

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-05-05 : 01:00:15

There is one more way...


SELECT *  FROM #temp 
WHERE     UniqueID IN (SELECT MIN(UniqueID) OVER(PARTITION BY CustNmbr) FROM #temp)

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

spunkiegirl
Starting Member

9 Posts

Posted - 2014-05-05 : 09:51:25

Thank you all so much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources