How to check for duplicate 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

How to check for duplicate records

Author

Topic

jpockets
Starting Member

45 Posts

Posted - 2007-07-27 : 14:36:53

I have this simple query But i want to check if the policy number appears more than once, I tried to use a count(RR.X_POLICY_NO)>1 in the where clause but i get an error, anybody have any suggestions?

QUERY:

Select 
RR.X_POLICY_NO,
RR.X_POLICY_EFCTV_DT,
RR.X_ASCO_CD,
RR.PRODUCT_RENWL_ABBR,


From RR 
WHERE 
 
year(rr.X_POLICY_XPRTN_DT)>=2005
Group By
RR.X_POLICY_NO,
RR.X_POLICY_EFCTV_DT,
RR.X_ASCO_CD,
RR.PRODUCT_RENWL_ABBR
ORDER BY
rr.X_POLICY_NO

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-27 : 14:39:06

check here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jpockets
Starting Member

45 Posts

Posted - 2007-07-27 : 14:49:38

I searched the thread but all i found was how to delete a dup

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-27 : 15:03:49

Allright I looked for the article but couldnt find it.
The usual way is to use GROUP BY HAVING COUNT(*) > 1. But you are saying you did that already. so if you can you post some sample data and expected output we can see where your query is failing?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jpockets
Starting Member

45 Posts

Posted - 2007-07-27 : 15:33:22

Thanks for the help!

This is the query i'm using, but it's only returning 40 rows and the table has about 300,000 rows and there are more than 40 policies that have dups just not sure how to show it.

Select 
RR.X_POLICY_NO,
RR.X_POLICY_EFCTV_DT,
RR.X_ASCO_CD,
RR.PRODUCT_RENWL_ABBR



From  RR
WHERE 
year(rr.X_POLICY_XPRTN_DT)>=2005
Group By
RR.X_POLICY_NO,
RR.X_POLICY_EFCTV_DT,
RR.X_ASCO_CD,
RR.PRODUCT_RENWL_ABBR
HAVING COUNT(RR.X_POLICY_NO) > 1.
ORDER BY
rr.X_POLICY_NO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-27 : 15:56:51

Because you are grouping on not only X_POLICY_NO!
You are grouping on 4 different columns. And use this WHERE clause, it will speed up things.

SELECT		RR.X_POLICY_NO,
		RR.X_POLICY_EFCTV_DT,
		RR.X_ASCO_CD,
		RR.PRODUCT_RENWL_ABBR
FROM		RR
INNER JOIN	(
			SELECT		X_POLICY_NO
			FROM		RR
			WHERE		X_POLICY_XPRTN_DT >= '20050101'
			GROUP BY	X_POLICY_NO
			HAVING		COUNT(*) > 1
		) AS x ON x.X_POLICY_NO = RR.X_POLICY_NO
ORDER BY	RR.X_POLICY_NO

E 12°55'05.25"
N 56°04'39.16"

jpockets
Starting Member

45 Posts

Posted - 2007-07-27 : 16:05:30

THANK-YOU!!!!!!!!!!!!!!!!!!!

Got the results i needed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources