Find duplicates and display 1 based on 2nd value? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Find duplicates and display 1 based on 2nd value?

Author

Topic

hollyquinn
Starting Member

31 Posts

Posted - 2010-02-24 : 11:01:00

I have a situation where I need to find duplicate records in a table. Once I've found all the duplicates, I need display only one based on the record in another column.

So, I want to find duplicate entries in the column nvarchar15. I know I can do that with this:

SELECT nvarchar15
FROM AllUserData
WHERE and (nvarchar15 IN
(SELECT nvarchar15
FROM AllUserData
GROUP BY nvarchar15
HAVING COUNT(*) > 1))

Then once I find those duplicate records, I need to compare another column called nvarchar14. If the value is Original I need to display that record. If the value is Audited Fax, then that record will not be displayed.

Does anyone know how I can do this? Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-24 : 11:05:20

then why you need to actually find duplicates? wont this be enough?

DELETE t FROM Table t WHERE nvarchar14='Audited Fax'

to remove all audited fax ones

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

hollyquinn
Starting Member

31 Posts

Posted - 2010-02-24 : 11:15:22

Visakh, I can't delete them. They want to keep them for records purposes. What I'm trying to do is see if there are two versions, if there are only display original. If there is only one entry it's going to be audited fax and I would need to display that. Does that make better sense?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-24 : 11:16:54

ok. Are you using sql 2005 by the way?

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

hollyquinn
Starting Member

31 Posts

Posted - 2010-02-24 : 11:17:32

Yes, sorry it is sql 2005

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-24 : 11:25:39

ok here you go


SELECT nvarchar15,nvarchar14
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY nvarchar15 ORDER BY Cat) AS Seq,
nvarchar15,nvarchar14
FROM
(SELECT nvarchar15,nvarchar14,
CASE WHEN nvarchar14='Original' THEN 1 ELSE 2 END AS Cat
FROM YourTable
)t
)r
WHERE Seq=1

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

hollyquinn
Starting Member

31 Posts

Posted - 2010-02-24 : 11:39:49

quote:
Originally posted by visakh16

ok here you go

SELECT nvarchar15,nvarchar14
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY nvarchar15 ORDER BY Cat) AS Seq,
nvarchar15,nvarchar14
FROM
(SELECT nvarchar15,nvarchar14,
CASE WHEN nvarchar14='Original' THEN 1 ELSE 2 END AS Cat
FROM YourTable
)t
)r
WHERE Seq=1

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

Thank you. That works perfectly!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-24 : 11:40:11

welcome

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-25 : 02:09:46

Also see how you can effectively use row_number() function for various purposes
http://beyondrelational.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