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?
Advertisement
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