Select records minus the duplicates

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

Select records minus the duplicates

Author

Topic

aefusco
Starting Member

2 Posts

Posted - 2012-08-16 : 14:20:23

I have a table that contains records where one field has duplicate values that I don't want to display. Example:
Table Name: MyTable
Fields in MyTable: ID,Name,PolicyNum
Content of table:
ID - Name - PolicyNum
1 - Tony - 1234
2 - John - 5678
3 - Mike - 1234

Result I am looking for from the SQL:
2 - John - 5678

I've tried all kinds of Counts, Distincts, Group By's, Having, and I cannot seem to get it right! My actual table has many more fields, but the policynum field in the above table is the only field that I don't want to see the records that have duplicate policynums.
I greatly appreciate any help, guidance!
Tony

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-16 : 14:32:49

[code]
SELECT ID,Name,PolicyNum
FROM
(
SELECT COUNT(1) OVER (PARTITION BY PolicyNum) AS Occ,*
FROM table
)t
WHERE Occ=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aefusco
Starting Member

2 Posts

Posted - 2012-08-16 : 14:38:26

visakh16 - THANK YOU!!THANK YOU!! THANK YOU!!!!!!!!!!!!I G-R-E-A-T-L-Y APPRECIATE YOUR HELP!!!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-16 : 14:40:16

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources