SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 one record for duplicate entries
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

spunkiegirl
Starting Member

USA
9 Posts

Posted - 05/04/2014 :  06:06:18  Show Profile  Reply with Quote
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)

Singapore
17586 Posts

Posted - 05/04/2014 :  08:01:04  Show Profile  Reply with Quote
select *
from
(
    select *, rn = row_number() over (partition by CustNmbr order by UniqueID)
    from   CustNmbr
) d
where d.rn = 1



KH
Time is always against us

Go to Top of Page

MuralikrishnaVeera
Posting Yak Master

India
106 Posts

Posted - 05/05/2014 :  01:00:15  Show Profile  Reply with Quote
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.......
Go to Top of Page

spunkiegirl
Starting Member

USA
9 Posts

Posted - 05/05/2014 :  09:51:25  Show Profile  Reply with Quote
Thank you all so much!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000