Selecting 2nd or 3rd Duplicate in a recordset

Question

Post reply

Selecting 2nd or 3rd Duplicate in a recordset

Jacob Pressures

SSCertifiable

Points: 5804
More actions
June 24, 2009 at 5:46 am

#211200

Sometimes I encounter multiple duplicates in a recordset. I either want to delete the second or third one or modify it in some way.
Here is an example and ways I might want to modify the data:
Bldg, Job
100, Plumber
202, Plumber
301, Plumber
521, Security
523, Security
678, Security
In this case, I don't want to just select the distinct occurance. I either want to delete the second or third occurance--skipping the first or modify the second and third occurence. If i modified them, this might be an example:
Bldg, Job
100, Plumber
202, Plumber 202
301, Plumber 301
521, Security
523, Security 523
678, Security 678
or
Bldg, Job
100, Plumber
202, Plumber 2
301, Plumber 3
521, Security
523, Security 2
678, Security 3
Is a CTE the best way to handle this? Or is there a better way? CTEs are still a bit new to me and looks like a lot of work when all i want are a few simply query statements.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

You can use a CTE to create a partitioned row number and update this CTE.

DECLARE @T TABLE (Bldg INT, Job VARCHAR(20));

INSERT INTO @T

SELECT '100', 'Plumber'

UNION ALL SELECT '202', 'Plumber'

UNION ALL SELECT '301', 'Plumber'

UNION ALL SELECT '521', 'Security'

UNION ALL SELECT '523', 'Security'

UNION ALL SELECT '678', 'Security';

; WITH

cte AS

(

SELECT

Bldg,

Job,

ROW_NUMBER() OVER (PARTITION BY Job ORDER BY Job) RowNum

FROM @T

)

UPDATE cte SET

Job = Job + CONVERT(VARCHAR(10), Bldg)

WHERE RowNum != 1

SELECT * FROM @T

t.hitendra SSCommitted Points: 1620 More actions · Answer 2

Hi,

Is this what you need?

create table #BuildingJob(Bldg int,Job varchar(50))
insert into #BuildingJob
select 100, 'Plumber' UNION ALL
select 202, 'Plumber' UNION ALL
select 301, 'Plumber' UNION ALL
select 521, 'Security' UNION ALL
select 523, 'Security' UNION ALL
select 678, 'Security'
select Bldg,(Case When SrNo>1 then Job + convert(varchar,SrNo) else Job end) as Job
from (
select Bldg,Job,Row_Number() over (partition by Job order by Job) as SrNo
from #BuildingJob ) tbl
-- or you can try convert(varchar,Bldg) for other result
drop table #BuildingJob

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 3

I think you guys answered my question. Thanks! I was wondering if this could be done while avoiding CTEs or Temp tables. I guess not. THere is no way of picking just the second or third occurrence.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 4

Jacob Pressures (6/24/2009)
I think you guys answered my question. Thanks! I was wondering if this could be done while avoiding CTEs or Temp tables. I guess not. THere is no way of picking just the second or third occurrence.

t.hitendra's answer doesn't contain any of both 😉

Glad we could help

Paul White SSC Guru Points: 150467 More actions · Answer 5

Jacob Pressures (6/24/2009)
CTEs are still a bit new to me and looks like a lot of work when all i want are a few simply query statements.

I would encourage you to get to know common table expressions.

The non-recursive variety are easy-as and make code more readable - at least to me.

There is a great article on this on MSDN: http://msdn.microsoft.com/en-us/magazine/cc163346.aspx

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Pandian S SSChasing Mays Points: 625 More actions · Answer 6

It'll show SECOND & THIRD

;WITH CTEs

AS (SELECT Bldg,Job,DENSE_RANK() OVER(PARTITION BY Job ORDER BY NEWID()) 'Repeats' FROM #BuildingJob)

SELECT * FROM CTEs WHERE Repeats IN(2,3)