CODEWITHSUNDEEP
sql-server
Pimpy
Pimpy

Reputation: 51

Set max count of duplicates

I have a table like this...

    Name
------------
George
George    
George
George
John
John   
John
Paul
Paul
Arnold
.

and I need result with max number of duplicates. Forexaple max 2 duplicates.

 Name
------------
George
George
John
John
Paul
Arnold
.

How I write the script? Thanks

Upvotes: 0

Views: 54

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460208

You can use ROW_NUMBER, for example:

WITH CTE AS
(
    SELECT RN = ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Name)
         , Name
    FROM dbo.Names
)
SELECT Name FROM CTE WHERE RN <= 2

Demo

Upvotes: 1

Related Questions