Select two duplicate records if it has 3 duplicate records

Is there a way to get only two duplicate records in SQL Server if it has three duplicate records? Example I have records ’10/13/2020′, ’10/13/2020, ’10/13/2020′. as you can see, it has 3 duplicate records. What I want is I will select only two duplicate records. if it has count 4, and 5 and above, still 2 counts will get

IF EXISTS (
    SELECT [Date] Count(*)
    FROM tablename
    WHERE [Date] = '10/13/2020'
    GROUP BY [Date]
    HAVING COUNT(*) > 1
)

Current output:

Date                      Count
2020-10-13 00:00:00.000   3

I want only to retrieve two counts, is that possible?

Answer

Perhaps something like this using the window functions

Example

;with cte as (
Select [date]
      ,RN  = row_number() over (partition by date order by date)
      ,Cnt = sum(1) over (partition by date)
 From  YourTable
)
Select *
 From  cte
 Where RN<=2 and Cnt>=3

Advertisement

Answer