Extracting duplicates within a specific range

Question

Post reply

Extracting duplicates within a specific range

Lozt-887152

SSC Veteran

Points: 253
More actions
December 2, 2010 at 6:42 am

#229706

Hi guys,
Can you offer advice on how to extract duplicates from a specific number range - please see example below:
SELECT Number, Firstname, Lastname, Email from dbo.Person
WHERE Number BETWEEN '10000' AND '11000' or
Number BETWEEN '13000' AND '14000' or
Number BETWEEN '18000' AND '19000' or
Number BETWEEN '20000' AND '23000' or
Number = '25141' or Number = '25144' or Number = '26221' or Number = '27889' or Number = '27899' or Number = '28888'
ORDER BY Number ASC
I'm unsure how to convert the below to suit my needs?
SELECT Extension
FROM dbo.Person
WHERE ...
GROUP BY Extension
HAVING ( COUNT(Extension) = 1 )
Many thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 1

Hello Lotz,

You can use Group By with Count(*) > 1 condition

Here is a script

SELECT

Number, Firstname, Lastname, Email

from dbo.Person

WHERE

Number BETWEEN '10000' AND '11000' or

Number BETWEEN '13000' AND '14000' or

Number BETWEEN '18000' AND '19000' or

Number BETWEEN '20000' AND '23000' or

Number = '25141' or Number = '25144' or Number = '26221' or Number = '27889' or Number = '27899' or Number = '28888'

Group by Number, Firstname, Lastname, Email

having COUNT(*) > 1

And also you can check articles delete duplicate records or ROW_NUMBER() OVER (PARTITION BY for removing duplicates

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

You might find it easier to use a temp table to hold your ranges. Finding the dupes is straightforward:

DROP TABLE #Person

CREATE TABLE #Person (Number CHAR(5), Firstname VARCHAR(20), Lastname VARCHAR(40), Email VARCHAR(50))

INSERT INTO #Person (Number, Firstname, Lastname, Email)

SELECT '18000', 'John', 'Smith', '' UNION ALL

SELECT '18000', 'Joe', 'Jones', '' UNION ALL

SELECT '13000', 'Mary', 'White', '' UNION ALL

SELECT '27889', 'Fred', 'Armstrong', '' UNION ALL

SELECT '23000', 'Steve', 'Carter', '' UNION ALL

SELECT '23000', 'Karthik', 'K', ''

DROP TABLE #NumberRanges

CREATE TABLE #NumberRanges (RangeStart CHAR(5), RangeEnd CHAR(5))

INSERT INTO #NumberRanges (RangeStart, RangeEnd)

SELECT '10000', '11000' UNION ALL

SELECT '13000', '14000' UNION ALL

SELECT '18000', '19000' UNION ALL

SELECT '20000', '23000'

;WITH DupeDetector AS (

SELECT Number, Firstname, Lastname, Email,

Instances = COUNT(*) OVER(PARTITION BY Number)

FROM #Person p

INNER JOIN #NumberRanges r ON p.number BETWEEN r.RangeStart AND r.RangeEnd

) SELECT * FROM DupeDetector WHERE Instances > 1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lozt-887152 SSC Veteran Points: 253 More actions · Answer 3

Eralper - a slight amendment to your code done the trick.

Many thanks for the responses guys.