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
x
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